Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005: Script all Primary Keys Expand / Collapse
Author
Message
Posted Saturday, September 29, 2007 6:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 12, 2008 7:59 AM
Points: 275, Visits: 3
Comments posted to this topic are about the item SQL Server 2005: Script all Primary Keys


Post #404359
Posted Thursday, May 15, 2008 1:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 12, 2012 7:13 AM
Points: 5, Visits: 49
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)

Post #501062
Posted Friday, August 22, 2008 7:26 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:59 AM
Points: 89, Visits: 165
It appears to be not handling foreign keys properly and scripting them up as Primary keys.
Post #557326
Posted Friday, January 30, 2009 7:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, July 20, 2014 1:08 PM
Points: 537, Visits: 1,918
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_

:D
Post #646935
Posted Tuesday, May 12, 2009 1:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:46 PM
Points: 19, Visits: 68
Add:
where CONSTRAINT_TYPE = 'PRIMARY KEY'

to avoid scripting out the foreign keys.
Post #715420
Posted Thursday, July 23, 2009 4:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:23 AM
Points: 12, Visits: 207
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 ???
Post #758086
Posted Thursday, March 17, 2011 1:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 6, 2014 11:27 PM
Points: 126, Visits: 505
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



Post #1079976
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse