Declarative Referential Integrity - 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:


USE AdventureWorksDW
GO

EXEC dbo.usp_GenerateCreateDropPKCommands NULL
EXEC dbo.usp_GenerateCreateDropPKCommands 'DimCustomer'


ċ
usp_GenerateCreateDropPKCommands.sql
(5k)
Andy Hughes,
Jul 5, 2012, 8:08 AM
Comments