SQL Server 2005: Script all Primary Keys

  • Oxygen

    SSC-Addicted

    Points: 469

    Comments posted to this topic are about the item SQL Server 2005: Script all Primary Keys

  • Dumas Jean Christophe

    Valued Member

    Points: 55

    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)

  • Putts

    SSCommitted

    Points: 1857

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

  • DBA-640728

    SSChampion

    Points: 12896

    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_

    😀

  • Gordo-174357

    Mr or Mrs. 500

    Points: 581

    Add:

    where CONSTRAINT_TYPE = 'PRIMARY KEY'

    to avoid scripting out the foreign keys.

  • lionofdezert

    SSC Enthusiast

    Points: 104

    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 ???

  • MudLuck

    SSCrazy

    Points: 2271

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply