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.key_ordinal
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
- Read these related tips:
- The Importance of SQL Server Foreign Keys
- Check out all Constraint Related Tips
Percy Reyes has a Bach. Systems Engineering and Certified Senior Database Administrator focused on Microsoft SQL Server Internals with over 15+ years of extensive experience managing database servers on SQL Server 6.5, 7.0, 2000, 2005, 2008, 2008 R2, 2012, 2014, 2016, and 2017. He has in-depth knowledge and strong experience in database administration of SQL Server including installation and upgrading the database server and client tools, allocation system storage and planning future storage requirements for the database system, Backup/Restore operations, Indexing, Statistics, Integrity Checks, Database Maintenance, Database Reduction, Storage Engine, Replication, Monitoring, Optimization and Performance Tuning, In-Memory OLTP, Security and Compliance, Scalability, High Availability, Mirroring, Log Shipping, AlwaysOn, Failover Clustering, FileStream, Partitioning Very Large Tables, Disaster Recovery, Troubleshooting, and internals issues. Percy is always contributing to the SQL Community via twitter (@percyreyes) and writing in his blog and also as a frequent speaker about SQL Server technologies for over 10+ years at local user group meetings, webcasts, and national conferences. He can be reached at https://www.percyreyes.com.
- MSSQLTips Awards: Rookie of the Year Contender – 2014
Thanks a lot for this, it was a big help!
One thing we noticed is that for the foreign keys we had some issues because it wasn’t adding ON DELETE/UPDATE {ACTION : CASCADE, SET NULL, etc.)
I added some support to add ON UPDATE/DELETE actions if needed if you wanna update the foreign key script:
— 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 @StrDeleteAction varchar(4000)
declare @OnDeleteAction varchar(4000)
declare @StrUpdateAction varchar(4000)
declare @OnUpdateAction varchar(4000)
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, delete_referential_action_desc OnDeleteAction, update_referential_action_desc OnUpdateAction
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, @OnDeleteAction, @OnUpdateAction
while (@@FETCH_STATUS=0)
begin
set @StrParentColumn=@StrParentColumn + ‘, ‘ + quotename(@ParentColumn)
set @StrReferencedColumn=@StrReferencedColumn + ‘, ‘ + quotename(@ReferencedColumn)
SET @StrDeleteAction = CASE
WHEN @OnDeleteAction = ‘CASCADE’ THEN ‘ON DELETE CASCADE’ + char(13)
WHEN @OnDeleteAction = ‘SET_NULL’ THEN ‘ON DELETE SET NULL’ + char(13)
WHEN @OnDeleteAction = ‘SET_DEFAULT’ THEN ‘ON DELETE SET DEFAULT’ + char(13)
ELSE ”
END
SET @StrUpdateAction = CASE
WHEN @OnUpdateAction = ‘CASCADE’ THEN ‘ON UPDATE CASCADE’ + char(13)
WHEN @OnUpdateAction = ‘SET_NULL’ THEN ‘ON UPDATE SET NULL’ + char(13)
WHEN @OnUpdateAction = ‘SET_DEFAULT’ THEN ‘ON UPDATE SET DEFAULT’ + char(13)
ELSE ”
END
fetch next from CursorFKDetails into @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn, @OnDeleteAction, @OnUpdateAction
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)
+ @StrUpdateAction
+ @StrDeleteAction
+ ‘GO’ + char(13)
print @TSQLCreationFK
fetch next from CursorFK into @ForeignKeyID
end
close CursorFK
deallocate CursorFK
FYI. Showed me Incorrect syntax next to GO.
Replacing char(13) with char(10) fixed that