SQL Server 2005: Script all Primary Keys

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Oxygen » SQL Server 2005: Script all Primary Keys

SQL Server 2005: Script all Primary Keys

Rate Topic

Display Mode

Topic Options

Author

Message

Oxygen

Posted Saturday, September 29, 2007 6:09 AM

SSC 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

Dumas Jean Christophe

Posted Thursday, May 15, 2008 1:34 AM

Forum 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

Putts

Posted Friday, August 22, 2008 7:26 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, June 11, 2012 7:34 AM
Points: 88, Visits: 153

It appears to be not handling foreign keys properly and scripting them up as Primary keys.

Post #557326

DBA-640728

Posted Friday, January 30, 2009 7:24 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:21 PM
Points: 496, Visits: 1,724

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

Gordo-174357

Posted Tuesday, May 12, 2009 1:26 PM

Grasshopper

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

lionofdezert

Posted Thursday, July 23, 2009 4:37 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 7:25 AM
Points: 12, Visits: 152

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

MudLuck

Posted Thursday, March 17, 2011 1:13 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 5:32 PM
Points: 125, Visits: 453

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 »

Permissions