Generate Commands to Create and Drop PKs

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 5 Jul 2012

Description

This SP makes use of the function to get primary key column details.

Returns 2 lists of commands:

• 1. Create all PKs in a database using the definitions already in place in the database

  2. Drop all PKs currently in place in the database

NOTE: No changes are made to database structure when running this SP - it only generates TSQL.

NOTE2: The index options in the 'WITH' command in the CREATE scripts were a little challenging to track down in the system tables. In these cases, I have set them to default values as specified in SQL BOL. At a later date, if I discover their whereabouts, I will update the scripts - unless someone can point me in the right direction? In any case, I don't ever remember seeing these options set to anything other than the default values (probably because they are so well hidden)

Code

Stored Procedure:

DROP PROCEDURE dbo.usp_GenerateCreateDropPKCommands

GO

CREATE PROCEDURE dbo.usp_GenerateCreateDropPKCommands(@TableName SYSNAME = NULL)

AS

/***************************************************************

Purpose: To generate commands to create or drop all PKs

No changes are made by this storted procedure

Author: ChillyDBA

History: 5 Jul 2012

****************************************************************/

CREATE TABLE #PKs

(

DatabaseName SYSNAME NULL,

PK_TableSchema SYSNAME NULL,

PK_TableName SYSNAME NULL,

PK_Schema SYSNAME NULL,

PK_Name SYSNAME NULL,

PK_ColumnName SYSNAME NULL,

PK_ColumnOrder INT,

Is_Padded INT,

No_Recompute INT,

Allow_Row_Locks INT,

Allow_Page_Locks INT,

FileGroupName VARCHAR(100),

IX_Type VARCHAR(100),

IX_Sort_Order VARCHAR(5)

)

CREATE TABLE #DropCommands

(

CommandID INT IDENTITY(1,1),

Command VARCHAR(8000)

)

CREATE TABLE #CreateCommands

(

CommandID INT IDENTITY(1,1),

Command VARCHAR(8000)

)

DECLARE

@Command VARCHAR(8000),

@PK_Name SYSNAME,

@PK_Columns VARCHAR(8000)

-- retrieve all PK information for the database

INSERT #PKs

(

DatabaseName,

PK_TableSchema,

PK_TableName,

PK_Schema,

PK_Name,

PK_ColumnName,

PK_ColumnOrder,

Is_Padded,

No_Recompute,

Allow_Row_Locks,

Allow_Page_Locks,

FileGroupName,

IX_Type,

IX_Sort_Order

)

SELECT

pkc.DatabaseName,

pkc.PK_TableSchema,

pkc.PK_TableName,

pkc.PK_Schema,

pkc.PK_Name,

pkc.PK_ColumnName,

pkc.PK_ColumnOrder,

si.Is_Padded,

s.No_Recompute,

si.Allow_Row_Locks,

si.Allow_Page_Locks,

fg.Name,

si.Type_Desc,

CASE sic.Is_Descending_Key WHEN 1 THEN 'DESC' ELSE 'ASC' END

FROM dbo.udf_GetPrimaryKeyColumns(@TableName) pkc

INNER JOIN sys.objects so

ON SCHEMA_ID(pkc.PK_Schema) = so.schema_id

AND pkc.PK_Name = so.name

INNER JOIN sys.indexes si

ON so.parent_object_id = si.OBJECT_ID

AND index_id = 1 --for PK index

INNER JOIN sys.index_columns sic

ON si.OBJECT_ID = sic.OBJECT_ID

AND si.index_id = sic.index_id

AND pkc.PK_ColumnOrder = sic.index_column_id

INNER JOIN sys.stats s

ON si.name = s.name

INNER JOIN sys.filegroups fg

ON si.data_space_id = fg.data_space_id

-- loop through all PK details to create the commands

SELECT @PK_Name = MIN(PK_Name)

FROM #PKs

WHILE @PK_Name IS NOT NULL

BEGIN

SELECT

@Command = '',

@PK_Columns = NULL

-- a useful technique to unpivot a list of columns

-- into a comma-separated list with no trailing comma

SELECT @PK_Columns = COALESCE (@PK_Columns + ', ', '') + '[' + PK_ColumnName + '] ' + IX_Sort_Order

FROM #PKs

WHERE PK_Name = @PK_Name

ORDER BY PK_ColumnOrder

-- create the DROP PK command

SELECT @Command = @Command

+ 'ALTER TABLE ['

+ PK_TableSchema

+ '].['

+ PK_TableName

+ '] DROP CONSTRAINT ['

+ PK_Name

+ ']'

FROM #PKs

WHERE PK_Name = @PK_Name

AND PK_ColumnOrder = 1 -- saves using a messy set of MAX() commands - the PK will always have a columns #1

INSERT #DropCommands(Command)

VALUES (@Command)

-- good practice to zero the variable as is the results of next query are NULL,

-- then the old value (drop command) will remain in the variable

SELECT @Command = ''

-- create the CREATE PK command

SELECT @Command = @Command

+ 'ALTER TABLE ['

+ PK_TableSchema

+ '].['

+ PK_TableName

+ '] ADD CONSTRAINT ['

+ PK_Name

+ '] PRIMARY KEY '

+ IX_Type

+ ' ('

+ @PK_Columns

+ ') '

+ ' WITH (PAD_INDEX =' + CASE Is_Padded WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ', STATISTICS_NORECOMPUTE =' + CASE No_Recompute WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ', SORT_IN_TEMPDB =OFF' -- not stored in sys.indexes so use default value

+ ', IGNORE_DUP_KEY =OFF' -- not stored in sys.indexes so use default value

+ ', ONLINE =OFF' -- not stored in sys.indexes so use default value

+ ', ALLOW_ROW_LOCKS =' + CASE Allow_Row_Locks WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ', ALLOW_PAGE_LOCKS =' + CASE Allow_Page_Locks WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ') ON ['

+ FileGroupName

+ ']'

FROM #PKs

WHERE PK_Name = @PK_Name

AND PK_ColumnOrder = 1 -- saves using a messy set of MAX() commands - the PK will always have a columns #1

INSERT #CreateCommands(Command)

VALUES (@Command)

SELECT @PK_Name = MIN(PK_Name)

FROM #PKs

WHERE PK_Name > @PK_Name

END

SELECT Command AS 'Commands to DROP PKs'

FROM #DropCommands

ORDER BY CommandID

SELECT Command AS 'Commands to CREATE PKs'

FROM #CreateCommands

ORDER BY CommandID

DROP TABLE #DropCommands

DROP TABLE #CreateCommands

DROP TABLE #PKs

Test Code: