add PK for a primary key table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

add PK for a primary key table

Author

Topic

mobasha
Starting Member

45 Posts

Posted - 2007-05-10 : 17:59:59

Hi every one,,, I really need ur help in this my case like this : I have more than 200 table and some have primary keys and some have foreign keys and I must add a column for each table but this column should be a primary key on the table which have one and to be a foreign key on the one which have one . do u have any idea about what option that I have????
Cos this problem could be a disaster for my project………

MobashA

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-05-10 : 18:09:34

Maybe your project already is a disaster!

Maybe your tables already have "primary keys" but are not defined,
if not, adding a column and make it pk will not help.

rockmoose

mobasha
Starting Member

45 Posts

Posted - 2007-05-10 : 18:20:03

lets but it this way i have a master database which i have created by generating scripts from excisting database and this master gets its data from four regional data bases
so in the master after i exec the script as it is ,i need to add field in order to prevant duplication in primary keys >>

MobashA

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-05-10 : 18:48:17

Add region to the primary key of each table. If the data is disjoint.

If you have common data (like customers) in the regional databases you will have to clean and integrate that in you master database.

rockmoose

mobasha
Starting Member

45 Posts

Posted - 2007-05-10 : 19:08:03

the master database is empty and most of the keys r identity
--Any way when I tried to alter the table in order to add PK for a start I get an error messages “can’t add primary key the table already have on ”
MobashA

mobasha
Starting Member

45 Posts

Posted - 2007-05-11 : 14:24:32

hi i thought i should share this with u and thanks for ur effort
----------------
carete procedure get_tables_script_and_create
@source_server varchar(100),
@sourc_DB varchar(100),
@des_server varchar(100),
@des_DB varchar(100)
as
declare @tblname varchar(200),
@x1 varchar(100),
@x2 varchar(100),@x3 varchar(8000)

begin
set @x1=@source_server
set @x2=@sourc_DB

declare @sqlstring varchar(8000)
declare @statement varchar(8000)
--declare @def varchar(100)
--set @def=@des_DB+'_TablesDef'
declare cur cursor for select tablename from dts_TablesDef
order by dropsequence desc
open cur
fetch next from cur into @tblname
while(@@fetch_status=0)
begin
declare @x varchar(8000)
exec proc_genscript
@ServerName = @x1,
@DBName = @x2,
@ObjectName = @tblname,
@ObjectType = 'table',
@TableName = @tblname,
@ScriptFile = '',
@x=@sqlstring output
set @sqlstring= replace(@sqlstring,'go',' ')
--set @sqlstring= replace(@sqlstring,')) ON [PRIMARY]',',[dbname] ')
set @statement='use '+@des_DB+' '+@sqlstring
if(@statement like '% PRIMARY KEY CLUSTERED %')
begin
set @X=replace(@statement,') ON [PRIMARY]
) ON [PRIMARY]',',[test]) ON [PRIMARY]
) ON [PRIMARY]')
end
else if(@statement like '% FOREIGN KEY%')
begin

set @x3=replace(@statement,') REFERENCES',',[test] ) REFERENCES')
set @x=replace(@x3,')
) ON [PRIMARY]',',[test] )
) ON [PRIMARY]')
end
else
begin
set @x=@statement
END
--print ( @statement)
exec(@x)
fetch next from cur into @tblname

end
--declare @x1 varchar(8000)

close cur
deallocate cur

--
end
------------------------------

MobashA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources