mssqltips logo

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

By:   |   Updated: 2019-10-31   |   Comments (15)   |   Related: More > Constraints

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 [email protected]
 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)[email protected] and [email protected] and [email protected]
 order by ixc.key_ordinal
 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
    set @[email protected] + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @[email protected]  + @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 + [email protected] +  '('[email protected]+') '+ 
 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 [email protected]
 open CursorFKDetails
 fetch next from CursorFKDetails into  @ForeignKeyName, @ParentTableSchema, @ParentTableName, @ParentColumn, @ReferencedTableSchema, @ReferencedTable, @ReferencedColumn
 while (@@FETCH_STATUS=0)
 begin    
  set @[email protected] + ', ' + quotename(@ParentColumn)
  set @[email protected] + ', ' + 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 Updated: 2019-10-31


get scripts

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, November 19, 2019 - 8:25:10 AM - Nirmal Patel Back To Top

Thanks Percy Reyes for writing the very userful things.

I am refering this script and found one scenrio when this script will fail (for "Primary Key and Unique Constraint Creation Script"). Scenarios is, when you have included columns in any index then it will not populate @indexcolumns variable and will return blank value in @IndexColumns variable. So below is my suggested code vs existing. Thanks again for mainting this script, which reduce effort and maintain accuracy.

Existing code:

if @IsIncludedColumn=0 
    set @[email protected] + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @[email protected]  + @ColumnName  +', ' 
 

Suggested Code: (remove if condition when set indexcolumns)

set @[email protected] + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
 
if @IsIncludedColumn > 0 
 
   set @[email protected]  + @ColumnName  +', ' 

Thursday, October 31, 2019 - 12:09:33 PM - Percy Reyes Back To Top

Hi all,

Thank you all for your comments and suggestions. The very first script has been updated. now it is ordered by the key ordinal instead of column id.

Also, I am going to address the other issues in another tip to be published next month. 

Thanks,


Wednesday, October 02, 2019 - 4:29:22 PM - JBrune Back To Top

Thanks for the great post and all the hard work.  I added the new 'IF EXISTS' to the drop statements just to avoid needless errors on re-running.


Thursday, September 05, 2019 - 10:58:15 AM - Tony Trus Back To Top

I second Jagan's comment about the script for adding the constraints/keys having a bug in it with the column order. The key_ordinal is absolutely the needed order and not the index_column_id.  While the script will not error, the constraints created have a very real chance of being out of order.  This comes into play when putting back foreign keys where the column order must match exactly or the FK will return an error that the reference table doesn't have reference columns that match.   As of 9/5/2019 the code in this article has not been updated with this correction.


Tuesday, August 27, 2019 - 9:27:38 AM - Roberty Back To Top

Hello Percy,

Please, include order by in script to create foreing key, i try to recreate fk in my database and many fk constraint not create, because order columns pk was diferent fk, i fix it the problem with code below

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 [email protected]
order by fkc.referenced_column_id ASC

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 [email protected]
   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)[email protected] and [email protected] and [email protected]
    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 @[email protected] + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
    else
     set @[email protected]  + @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 + [email protected] +  '('[email protected]+') '+
   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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools