September 29, 2007 at 6:09 am
Comments posted to this topic are about the item SQL Server 2005: Script all Primary Keys
May 15, 2008 at 1:34 am
You can script the Fill Factor too :
Neat the end, just after :
SET @PKSQL = @PKSQL + ')'
Add :
DECLARE @ff TinyInt
SELECT @ff = (select A.fill_factor FROM sys.indexes A Where A.name=@PkName)
SET @PKSQL = @PKSQL + ' WITH FILLFACTOR= ' + CAST(@ff as NVARCHAR)
August 22, 2008 at 7:26 am
It appears to be not handling foreign keys properly and scripting them up as Primary keys.
January 30, 2009 at 7:24 am
i fixed the problem by
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE '%PK%' --adding this line
ORDER BY TABLE_NAME
and it worked for me since i have all my primary keys starting with PK_
May 12, 2009 at 1:26 pm
Add:
where CONSTRAINT_TYPE = 'PRIMARY KEY'
to avoid scripting out the foreign keys.
July 23, 2009 at 4:37 am
First thing which is already mentioned that, its not working properly as it dealing FK as PK too, and second thing, what if, i have more then one filegroups ???
March 17, 2011 at 1:13 pm
Oxygen thank you for the post your code got me 80 % of the way there. I modified your cursor to look for PK in the db that are non clustered then rebuild as clustered. I found 32 in first db looked. isn't rolling on to a new company great?
SELECT tc.TABLE_NAME, tc.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
inner join sys.indexes i
on tc.CONSTRAINT_NAME= i.name
where CONSTRAINT_TYPE = 'PRIMARY KEY'
and i.index_id <> 1
ORDER BY TABLE_NAME
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy