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