March 23, 2009 at 10:17 pm
Hi Experts,
I have more than 100 tables in a database and is there any way to create primary key to all tables in a go.Please help me as I am new to SQL Server Development.
TIA
March 24, 2009 at 6:08 am
I doubt there's a way to do this in a single go. Each table will need and individual PK created. Unless you've named the primary key in all 100 tables ID or something (a horrible idea), you can't easily generate code to create it. However, if you're intent on trying, try using a combination of sp_msforeachtable and sp_executesql to generate ad hoc queries.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 24, 2009 at 10:27 pm
.
March 24, 2009 at 10:32 pm
Thanks Grant.
My idea was to create a procedure like
create procedure rkn_pk
declare @tabname char(50)
declare @colname char(50)
as
alter table @tabname add constraint @tabname+pk primary key on @colname
Is that possible.
TIA
March 25, 2009 at 1:30 am
Created this but getting error while executing as the constraint name is creating lot of space
alter procedure rkn_pk
@tabname char(50),
@colname char(50)
as
begin
declare @sql as varchar(500)
select @sql='alter table '+ @tabname +'add constraint '+ @tabname+'_pk1'+' primary key'+ '('+@colname+')'
print(@sql)
end
Please help
March 25, 2009 at 4:59 am
Solved
March 25, 2009 at 9:18 pm
Hi Ratheesh,
Can you please give me the code that solved your issue.
Iam also looking for the same query and conditions.
Thanks,
Sandhya.
March 26, 2009 at 1:51 am
ALTER procedure [dbo].[rkn_pk]
@tabname char(50),
@colname char(50)
as
begin
declare @sql as varchar(500)
select @sql='alter table '+ RTRIM(@tabname) +' add constraint '+ RTRIM(@tabname)+'_pk1' + ' primary key'+ '('+RTRIM(@colname)+')'
execute (@sql)
end
EXEC [rkn_pk] 'RKN','NO'
March 26, 2009 at 6:07 am
Hi
I would advice to use QUOTENAME to avoid SQL injections:
ALTER PROCEDURE [dbo].[rkn_pk]
@tabname CHAR(50),
@colname CHAR(50)
AS
BEGIN
SELECT @tabname = LTRIM(RTRIM(@tabname)), @colname = LTRIM(RTRIM(@colname))
DECLARE @pk_name AS VARCHAR(100)
SELECT @pk_name = @tabname + '_pk1'
DECLARE @sql AS VARCHAR(500)
SELECT @sql = 'ALTER TABLE '+ QUOTENAME(@tabname) + ' ADD CONSTRAINT ' + QUOTENAME(@pk_name) + ' PRIMARY KEY ' + ' ('+ QUOTENAME(@colname) + ')'
EXECUTE (@sql)
END
Two other questions:
Why do you use CHAR instead of VARCHAR?
Why do you not script your PRIMARY KEY as CLUSTERED?
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 26, 2009 at 7:40 am
Thanks Florian for the Tips,
Actually created this as an experiment. I want to convert this to DB2 which i dont know..Do you have any idea? I will be very thankful if you can help me in doing that.
TIA
March 26, 2009 at 10:23 am
Hi Ratheesh
Sorry, I have absolutely no idea about DB2.
Good luck
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 28, 2009 at 8:17 am
By default, a column declared as a PRIMARY KEY should be clustered. Looking at the script I did not see anything about dropping an existing clustered index prior to creating the PRIMARY KEY.
In addition to adding the PRIMARY KEY (a serious design error) you should also take a good like at any index present in the database as well.
Regards
March 28, 2009 at 10:42 am
J (3/28/2009)
By default, a column declared as a PRIMARY KEY should be clustered. Looking at the script I did not see anything about dropping an existing clustered index prior to creating the PRIMARY KEY.
Did you notice my question below my posted script? 😉
I just didn't add the CLUSTERED option because the previous script did not contain it.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply