3

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no syntax errors as it displays the msg that says "Alter Table [ParentTable] Drop Constraint FK_Name"... for a total of 23 keys, but yet when I refresh the Server or the DB, and Tables, I still see them when I expand the Keys node for each table? so why its not dropping them? Also, do I need to convert it to a permanent table or put the script in a stored procedure in case if I want to run it again for testing purposes? Please advice.

I used the following script from "Swastik Mishra" posted by "Pinal Dave" http://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/

    SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema,     ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +      ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '

GO’
FROM
@table
–ADD CONSTRAINT:
SELECT
‘
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' +   ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ‘ + ForeignKeyConstraintName + ‘ FOREIGN KEY(‘ +   ForeignKeyConstraintColumnName + ‘) REFERENCES [' + PrimaryKeyConstraintTableSchema + ']. [' + PrimaryKeyConstraintTableName + '](‘ + PrimaryKeyConstraintColumnName + ‘)

GO’
FROM
@table
GO

closed as off-topic by Max Vernon, Paul White, Kin Shah, RLF, Mark Storey-Smith Sep 19 '14 at 17:30

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, Paul White, Kin Shah, RLF, Mark Storey-Smith
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    All this does is generate the drop constraint sql statements to the output window. It doesn't actually run them. You then need to run the statements it outputs in the query editing window – Mark Sinkinson Sep 18 '14 at 19:03
  • Wow really, I will give it a try right now – Shayma Ahmad Sep 18 '14 at 19:04
  • I get an error about an incorrect syntax near 'GO' which i see it in the displayed msg too. – Shayma Ahmad Sep 18 '14 at 19:13
  • 6
    @ShaymaAhmad so now you know. Don't get your scripts from Pinal Dave. You are forgiven to do it once, as the site's title is at least, misleading, but not again. – ypercubeᵀᴹ Sep 18 '14 at 19:46
  • 1
    And feel free to point Pinal Dave here - I'm sure he'd be interested in improving his script. – Aaron Bertrand Sep 18 '14 at 20:46
15

Here's a better script.

  1. It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints.
  2. It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like SSMS, not a T-SQL keyword).
  3. It doesn't have incorrectly copied apostrophes in place of single quotes ( vs. ').
  4. It deals with multi-column foreign key constraints (the code you have now assumes that all foreign keys are only defined against single columns).

The remainder is taken from my blog post, Four Practical Use Cases for Grouped Concatenation. It's script #4.


The drop is easy; just build a simple list of ALTER TABLE commands from sys.foreign_keys (I've also added commented-out filters that could be used to limit the scope to referenced tables matching a naming pattern or existing within a certain schema):

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
-- WHERE fk.referenced_object_id IN 
-- (SELECT [object_id] FROM sys.tables WHERE name LIKE N'%some pattern%')
-- WHERE fk.referenced_schema_id IN 
-- (SELECT [schema_id] FROM sys.schemas WHERE name = N'some_schema')

PRINT @drop;

Don't execute that yet! Of course, you need to generate the CREATE commands while they still exist. Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column (the multi-column situation is where grouped concatenation comes in real handy):

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
--WHERE rt.name LIKE N'%some pattern%'
--WHERE rs.name = N'some_schema'

PRINT @create;

When you're happy with the output (keep in mind that PRINT is limited to 8K, so it may look like the command is truncated), add this to the end, and run it again:

EXEC sp_executesql @drop;

-- drop and re-create the table here, whatever that needs to entail

EXEC sp_executesql @create;
  • Thank you so much for posting your script. I just have to figuer out why its dropping more constraints (34 foreign keys) versus creating or adding only 23. So I restore the database again from a fresh backup copy and its the same thing! I realized later that few of the foreign keys the script is dropping is not a constraint and when I attempt to add them back, I get an error of a conflict of one of the FKs, but its adding back the rest. You were right about the PRINT so I run the EXEC sp_executesql at Create or drop but it's still truncating so I type the missing ones. – Shayma Ahmad Sep 24 '14 at 15:49
  • @Shayma I have no idea how to determine how it could drop 34 and create 23. Are you running the create script after you've executed (part of) the drop script? It would only be truncating anything (how do you know it's truncating anything?) if you chose something other than NVARCHAR(MAX) for the variables. – Aaron Bertrand Sep 24 '14 at 15:52
  • I'm running the Create script right after my full backup restore, so I get 24 and then I select everything from Sys.Foreign_Keys that gives me all my current FKs of 34, which tells me that the Create script is truncating the rest of them as the last Alter statement (24th) in the output of the Create script is missing the name of the Foreign key that its referencing, so I manually type the rest of it when I paste it into my query editor window, so it seems I have to see which ones are also missing to total it to 34 Alter Add statements for each drop. – Shayma Ahmad Sep 24 '14 at 16:57
  • 1
    Sorry, I don't know how to help debug this, but I can assure you the command is not being truncated. If you're trying to validate the contents of the variables using PRINT or SELECT, these will be limited by the output support of those commands (see mssqltips.com/sqlservertip/3185/…). So if you are printing the commands and then copying the printout and executing it, stop doing that! If you need to store the commands between a backup and a restore, insert the variables into a table in a different database. – Aaron Bertrand Sep 24 '14 at 17:01
  • That probably also explains why the drop script is longer - it gets cut off later because the drop command for each constraint will be fewer characters than the equivalent create command (since the drop only names the constraint, but the create has to list the target table and the columns)... – Aaron Bertrand Sep 24 '14 at 17:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.