Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL


By:   |   Read Comments (10)   |   Related Tips: More > Constraints

Attend these FREE MSSQLTips webcasts >> click to register


Problem

As SQL Server DBAs we may need to generate a script for the creation of all Primary Keys, Unique and Foreign Key constraints. We know with the SQL Server native tools that there is not a way to meet this need all at the same time. In this tip we look at two simple scripts to generate the SQL Server Primary Keys, Unique and Foreign Key constraints and also provide two scripts for dropping these constraints.

Solution

Common SQL Server constraints are for Primary and Foreign Keys as well as Unique constraints.  They are very important for data integrity in SQL Server databases. This is why we need to be able to take a backup of each type of constraint in an efficient manner so that we can recreate them in case they are dropped by accident or if you need to recreate the same constraints in another copy of the same database for testing, development or training purposes. The scripts below have been written in SQL Server 2014 but they should also work on SQL Server 2005/2008/2008R/2012.

SQL Server Primary Key and Unique Constraint Creation Script

The following script is for the creation of all Primary Keys and Unique Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
declare @SchemaName varchar(100)
declare @TableName varchar(256) declare @IndexName varchar(256) declare @ColumnName varchar(100) declare @is_unique_constraint varchar(100) declare @IndexTypeDesc varchar(100) declare @FileGroupName varchar(100) declare @is_disabled varchar(100) declare @IndexOptions varchar(max) declare @IndexColumnId int declare @IsDescendingKey int declare @IsIncludedColumn int declare @TSQLScripCreationIndex varchar(max) declare @TSQLScripDisableIndex varchar(max) declare @is_primary_key varchar(100) declare CursorIndex cursor for select schema_name(t.schema_id) [schema_name], t.name, ix.name, case when ix.is_unique_constraint = 1 then ' UNIQUE ' else '' END ,case when ix.is_primary_key = 1 then ' PRIMARY KEY ' else '' END , ix.type_desc, case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end + case when ix.allow_row_locks=1 then 'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions , FILEGROUP_NAME(ix.data_space_id) FileGroupName from sys.tables t inner join sys.indexes ix on t.object_id=ix.object_id where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1) --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName and t.is_ms_shipped=0 and t.name<>'sysdiagrams' order by schema_name(t.schema_id), t.name, ix.name open CursorIndex fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName while (@@fetch_status=0) begin declare @IndexColumns varchar(max) declare @IncludedColumns varchar(max) set @IndexColumns='' set @IncludedColumns='' declare CursorIndexColumn cursor for select col.name, ixc.is_descending_key, ixc.is_included_column from sys.tables tb inner join sys.indexes ix on tb.object_id=ix.object_id inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1) and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName order by ixc.index_column_id open CursorIndexColumn fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn while (@@fetch_status=0) begin if @IsIncludedColumn=0 set @IndexColumns=@IndexColumns + @ColumnName + case when @IsDescendingKey=1 then ' DESC, ' else ' ASC, ' end else set @IncludedColumns=@IncludedColumns + @ColumnName +', ' fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn end close CursorIndexColumn deallocate CursorIndexColumn set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1) set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end -- print @IndexColumns -- print @IncludedColumns set @TSQLScripCreationIndex ='' set @TSQLScripDisableIndex ='' set @TSQLScripCreationIndex='ALTER TABLE '+ QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + @is_unique_constraint + @is_primary_key + +@IndexTypeDesc + '('+@IndexColumns+') '+ case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';' print @TSQLScripCreationIndex print @TSQLScripDisableIndex fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName end close CursorIndex deallocate CursorIndex

Script to Drop all SQL Server Primary Key and Unique Constraints

The following script is to drop all Primary Keys and Unique Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE DROP SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @TSQLDropIndex VARCHAR(MAX) DECLARE CursorIndexes CURSOR FOR SELECT schema_name(t.schema_id), t.name, i.name FROM sys.indexes i INNER JOIN sys.tables t ON t.object_id= i.object_id WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams' and (is_primary_key=1 or is_unique_constraint=1) OPEN CursorIndexes FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName WHILE @@fetch_status = 0 BEGIN SET @TSQLDropIndex = 'ALTER TABLE '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' +QUOTENAME(@IndexName) PRINT @TSQLDropIndex FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName END CLOSE CursorIndexes DEALLOCATE CursorIndexes

SQL Server Foreign Key Constraint Creation Script

The following script is for the creation of all Foreign Keys Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyID int
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000)
declare @ParentColumn varchar(4000)
declare @ReferencedTable varchar(4000)
declare @ReferencedColumn varchar(4000)
declare @StrParentColumn varchar(max)
declare @StrReferencedColumn varchar(max)
declare @ParentTableSchema varchar(4000)
declare @ReferencedTableSchema varchar(4000)
declare @TSQLCreationFK varchar(max)
--Written by Percy Reyes www.percyreyes.com
declare CursorFK cursor for select object_id--, name, object_name( parent_object_id) 
from sys.foreign_keys
open CursorFK
fetch next from CursorFK into @ForeignKeyID
while (@@FETCH_STATUS=0)
begin
 set @StrParentColumn=''
 set @StrReferencedColumn=''
 declare CursorFKDetails cursor for
  select  fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
  object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
   object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
  from --sys.tables t inner join 
  sys.foreign_keys fk 
  inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
  inner join sys.columns c1 on c1.object_id=fkc.parent_object_id and c1.column_id=fkc.parent_column_id 
  inner join sys.columns c2 on c2.object_id=fkc.referenced_object_id and c2.column_id=fkc.referenced_column_id 
  inner join sys.tables t1 on t1.object_id=fkc.parent_object_id 
  inner join sys.tables t2 on t2.object_id=fkc.referenced_object_id 
  where fk.object_id=@ForeignKeyID
 open CursorFKDetails
 fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 while (@@FETCH_STATUS=0)
 begin    
  set @StrParentColumn=@StrParentColumn + ', ' + quotename(@ParentColumn)
  set @StrReferencedColumn=@StrReferencedColumn + ', ' + quotename(@ReferencedColumn)
  
     fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 end
 close CursorFKDetails
 deallocate CursorFKDetails

 set @StrParentColumn=substring(@StrParentColumn,2,len(@StrParentColumn)-1)
 set @StrReferencedColumn=substring(@StrReferencedColumn,2,len(@StrReferencedColumn)-1)
 set @TSQLCreationFK='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' WITH CHECK ADD CONSTRAINT '+quotename(@ForeignKeyName)
 + ' FOREIGN KEY('+ltrim(@StrParentColumn)+') '+ char(13) +'REFERENCES '+quotename(@ReferencedTableSchema)+'.'+quotename(@ReferencedTable)+' ('+ltrim(@StrReferencedColumn)+') ' + char(13)+'GO'
 
 print @TSQLCreationFK

fetch next from CursorFK into @ForeignKeyID 
end
close CursorFK
deallocate CursorFK

Script to Drop all SQL Server Foreign Key Constraints

The following script is to drop all Foreign Key Constraints in the SQL Server database:

--- SCRIPT TO GENERATE THE DROP SCRIPT OF ALL FOREIGN KEY CONSTRAINTS
declare @ForeignKeyName varchar(4000)
declare @ParentTableName varchar(4000) declare @ParentTableSchema varchar(4000) declare @TSQLDropFK varchar(max) declare CursorFK cursor for select fk.name ForeignKeyName, schema_name(t.schema_id) ParentTableSchema, t.name ParentTableName from sys.foreign_keys fk inner join sys.tables t on fk.parent_object_id=t.object_id open CursorFK fetch next from CursorFK into @ForeignKeyName, @ParentTableSchema, @ParentTableName while (@@FETCH_STATUS=0) begin set @TSQLDropFK ='ALTER TABLE '+quotename(@ParentTableSchema)+'.'+quotename(@ParentTableName)+' DROP CONSTRAINT '+quotename(@ForeignKeyName)+ char(13) + 'GO' print @TSQLDropFK fetch next from CursorFK into @ForeignKeyName, @ParentTableSchema, @ParentTableName end close CursorFK deallocate CursorFK

 

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, June 28, 2017 - 1:54:14 PM - Laura Back To Top

 hello Percy,

thanks for you sharing, but i think for the Foreign Key Constraints scripts, should be adding Cascading Referential Integrity Constraints part.

BR,Laura

 


Friday, August 05, 2016 - 5:19:52 AM - Pedro Back To Top

 

If you run all the scripts against a database, Number of create scripts differ from the drop scripts.

Anybody noticed that, or am I doing something wrong?

 


Thursday, July 28, 2016 - 7:28:15 AM - Carlo Borreo Back To Top

The scripts are exactly what I was looking for.

The "SQL Server Primary Key and Unique Constraint Creation Script" runs ok.
I have a problem executing the "SQL Server Foreign Key Constraint Creation Script" like this:

Invalid length parameter passed to the LEFT or SUBSTRING function

There are two SUBSTRING commands, not sure which one is causing it.


Wednesday, March 30, 2016 - 2:22:54 PM - Jagan Kannappan Back To Top

I had to make a small change to PK/unique constraints script to order index_columns by key_ordinal rather than the column_id.

Changed order by ixc.index_column_id to order by ixc.key_ordinal  


Saturday, July 25, 2015 - 1:34:46 PM - Enzo Back To Top

 

Hello, I tried your script successfully, but how should you do if you want to copy the identity (1,1) of the primary key?

 


Wednesday, February 25, 2015 - 4:15:43 AM - Thomas Franz Back To Top

Beside the fact, that the foreign keys script does not add the cascade options (could be done by adding and replacing of _ by space in the *referential_action_desc-fields of sys.foreign_keys), the primary-key script does not work for partitioned tables / indexes.

So I modfied it by joining sys.dataspaces (instead of using the FILEGROUP_NAME-function which will fail because the dataspace_id for partitions is > 64k and the function accepts only smallints) and querying the partition column name (ok, it would be a little bit faster, if I had done it in the inner cursor):

 

--- SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS.
declare @SchemaName varchar(100)
declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique_constraint varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare @is_primary_key varchar(100)
declare @PartitionColumn varchar(100)

declare CursorIndex cursor for
SELECT Schema_name(t.schema_id)             [schema_name],
       t.NAME,
       ix.NAME,
       CASE
         WHEN ix.is_unique_constraint = 1 THEN ' UNIQUE '
         ELSE ''
       END,
       CASE
         WHEN ix.is_primary_key = 1 THEN ' PRIMARY KEY '
         ELSE ''
       END,
       ix.type_desc,
       CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END + CASE WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END + CASE WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END + CASE WHEN Indexproperty(t.object_id, ix.NAME, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END + CASE WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END
       + 'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
       + Cast(ix.fill_factor AS VARCHAR(3)) AS IndexOptions,
       ds.NAME                              FileGroupName,
       CASE
         WHEN ds.type_desc = 'PARTITION_SCHEME' THEN (SELECT c.NAME
                                                        FROM sys.index_columns ic
                                                             INNER JOIN sys.columns c
                                                                     ON c.object_id = ix.object_id
                                                                        AND c.column_id = ic.column_id
                                                       WHERE ic.partition_ordinal = 1
                                                         AND ic.index_id = ix.index_id
                                                         AND ic.object_id = ix.object_id)
         ELSE NULL
       END                                  partition_column
  FROM sys.tables t
       INNER JOIN sys.indexes ix
               ON t.object_id = ix.object_id
       LEFT JOIN sys.data_spaces ds
              ON ds.data_space_id = ix.data_space_id
 WHERE 1 = 1
   AND ix.type > 0
   AND ( ix.is_primary_key = 1
          OR ix.is_unique_constraint = 1 ) --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
   AND t.is_ms_shipped = 0
   AND t.NAME <> 'sysdiagrams'
 ORDER BY Schema_name(t.schema_id),
          t.NAME,
          ix.NAME 

open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName, @PartitionColumn
while (@@fetch_status=0)
begin
   declare @IndexColumns varchar(max)
   declare @IncludedColumns varchar(max)
   set @IndexColumns=''
   set @IncludedColumns=''
   declare CursorIndexColumn cursor for
   select col.name, ixc.is_descending_key, ixc.is_included_column
     from sys.tables             tb
    inner join sys.indexes       ix  on tb.object_id  = ix.object_id
    inner join sys.index_columns ixc on ix.object_id  = ixc.object_id and ix.index_id   = ixc.index_id
    inner join sys.columns       col on ixc.object_id = col.object_id and ixc.column_id = col.column_id
   where ix.type>0 and (ix.is_primary_key=1 or ix.is_unique_constraint=1)
     and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
    order by ixc.index_column_id
   open CursorIndexColumn
   fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
   while (@@fetch_status=0)
   begin
    if @IsIncludedColumn=0
      set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
    else
     set @IncludedColumns=@IncludedColumns  + @ColumnName  +', '
      
    fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
   end
   close CursorIndexColumn
   deallocate CursorIndexColumn
   set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
   set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
  --  print @IndexColumns
  --  print @IncludedColumns
 
  set @TSQLScripCreationIndex =''
  set @TSQLScripDisableIndex =''
  set  @TSQLScripCreationIndex='ALTER TABLE '+  QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ADD CONSTRAINT ' +  QUOTENAME(@IndexName) + @is_unique_constraint + @is_primary_key + +@IndexTypeDesc +  '('+@IndexColumns+') '+
   case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + concat(QUOTENAME(@FileGroupName), '(' + @PartitionColumn + ')')  + ';' 
 
  print @TSQLScripCreationIndex
  print @TSQLScripDisableIndex

fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique_constraint, @is_primary_key, @IndexTypeDesc, @IndexOptions, @FileGroupName, @PartitionColumn

end
close CursorIndex
deallocate CursorIndex



Wednesday, January 07, 2015 - 7:56:58 AM - Azim Back To Top

Thank you, the information was very helpful.


Tuesday, January 06, 2015 - 12:41:48 PM - Greg Robidoux Back To Top

Thanks Richard and David for pointing out the issues., the errors have all been fixed.  Their was an issue with our web editing tool.

-Greg


Tuesday, January 06, 2015 - 11:54:39 AM - Richard Back To Top
Some errors in the scripts:
  • declare @SchemaName varchar(100)eclare @TableName varchar(256)

  • declare @ForeignKeyID inteclare @ForeignKeyName varchar(4000)
  • deadeallocate CursorFK
 

Tuesday, January 06, 2015 - 11:11:48 AM - David Wanta Back To Top

Good morning!

 

I was very interested in your script.  Thank you for providing this info.  I noticed a couple of issues with the very first script.  For example these lines:

declare @SchemaName varchar(100)eclare @TableName varchar(256)

deadeallocate CursorIndex

Would you mind double checking that the right/final version was included in the article before I dig in too far?

 

Thanks and have a great day,

David


Learn more about SQL Server tools