Stored procedure to remove FK of a given table

Question

I need to create a stored procedure that:

1. Accepts a table name as a parameter
2. Find its dependencies (FKs)
3. Removes them
4. Truncate the table

I created the following so far based on http://www.mssqltips.com/sqlservertip/1376/disable-enable-drop-and-recreate-sql-server-foreign-keys/ . My problem is that the following script successfully does 1 and 2 and generates queries to alter tables but does not actually execute them. In another word how can execute the resulting "Alter Table ..." queries to actually remove FKs?

CREATE PROCEDURE DropDependencies(@TableName VARCHAR(50))
AS
BEGIN
SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name
FROM sys.foreign_keys WHERE referenced_object_id=object_id(@TableName)
END



EXEC DropDependencies 'TableName'

Any idea is appreciated!

Update: I added the cursor to the SP but I still get and error: "Msg 203, Level 16, State 2, Procedure DropRestoreDependencies, Line 75 The name 'ALTER TABLE [dbo].[ChildTable] DROP CONSTRAINT [FK__ChileTable__ParentTable__745C7C5D]' is not a valid identifier."

Here is the updated SP: CREATE PROCEDURE DropRestoreDependencies(@schemaName sysname, @tableName sysname) AS BEGIN SET NOCOUNT ON

DECLARE @operation VARCHAR(10)  


SET @operation = 'DROP' --ENABLE, DISABLE, DROP  


DECLARE @cmd NVARCHAR(1000) 

DECLARE   
   @FK_NAME sysname,  
   @FK_OBJECTID INT,  
   @FK_DISABLED INT,  
   @FK_NOT_FOR_REPLICATION INT,  
   @DELETE_RULE    smallint,     
   @UPDATE_RULE    smallint,     
   @FKTABLE_NAME sysname,  
   @FKTABLE_OWNER sysname,  
   @PKTABLE_NAME sysname,  
   @PKTABLE_OWNER sysname,  
   @FKCOLUMN_NAME sysname,  
   @PKCOLUMN_NAME sysname,  
   @CONSTRAINT_COLID INT  


DECLARE cursor_fkeys CURSOR FOR   
   SELECT  Fk.name,  
           Fk.OBJECT_ID,   
           Fk.is_disabled,   
           Fk.is_not_for_replication,   
           Fk.delete_referential_action,   
           Fk.update_referential_action,   
           OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,   
           schema_name(Fk.schema_id) AS Fk_table_schema,   
           TbR.name AS Pk_table_name,   
           schema_name(TbR.schema_id) Pk_table_schema  
   FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
           sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join   
   WHERE   TbR.name = @tableName  
           AND schema_name(TbR.schema_id) = @schemaName  

OPEN cursor_fkeys  

FETCH NEXT FROM   cursor_fkeys   
   INTO @FK_NAME,@FK_OBJECTID,  
       @FK_DISABLED,  
       @FK_NOT_FOR_REPLICATION,  
       @DELETE_RULE,     
       @UPDATE_RULE,     
       @FKTABLE_NAME,  
       @FKTABLE_OWNER,  
       @PKTABLE_NAME,  
       @PKTABLE_OWNER  

WHILE @@FETCH_STATUS = 0   
BEGIN   

   -- create statement for dropping FK and also for recreating FK  
   IF @operation = 'DROP'  
   BEGIN  

       -- drop statement  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
       + ']  DROP CONSTRAINT [' + @FK_NAME + ']'     

      EXEC @cmd  

       -- create process  
       DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT  

       -- create cursor to get FK columns  
       DECLARE cursor_fkeyCols CURSOR FOR   
       SELECT  COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,   
               COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name  
       FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
               sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN   
               sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID   
       WHERE   TbR.name = @tableName  
               AND schema_name(TbR.schema_id) = @schemaName  
               AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008  
       ORDER BY Fk_Cl.constraint_column_id  

       OPEN cursor_fkeyCols  

       FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  

       SET @COUNTER = 1  
       SET @FKCOLUMNS = ''  
       SET @PKCOLUMNS = ''  

       WHILE @@FETCH_STATUS = 0   
       BEGIN   

           IF @COUNTER > 1   
           BEGIN  
               SET @FKCOLUMNS = @FKCOLUMNS + ','  
               SET @PKCOLUMNS = @PKCOLUMNS + ','  
           END  

           SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
           SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  

           SET @COUNTER = @COUNTER + 1  

           FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  
       END  

       CLOSE cursor_fkeyCols   
       DEALLOCATE cursor_fkeyCols   



   END  

   FETCH NEXT FROM    cursor_fkeys   
      INTO @FK_NAME,@FK_OBJECTID,  
           @FK_DISABLED,  
           @FK_NOT_FOR_REPLICATION,  
           @DELETE_RULE,     
           @UPDATE_RULE,     
           @FKTABLE_NAME,  
           @FKTABLE_OWNER,  
           @PKTABLE_NAME,  
           @PKTABLE_OWNER  
END  

CLOSE cursor_fkeys   
DEALLOCATE cursor_fkeys 
END

For running use:

EXEC DropRestoreDependencies dbo, ParentTable

Answer 1

Use a cursor to go through your SELECT results, populating a variable with the single column, and executing that query with EXEC(@YourVariable). Be sure to use parens around the variable!

Answer 2

The issue is that you are only preparing the SQL statement and not executing it (I think)

CREATE PROCEDURE DropDependencies(@TableName VARCHAR(50))
AS
BEGIN
DECLARE @SQL nvarchar(max)
SELECT @SQL = 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT ' + name
FROM sys.foreign_keys WHERE referenced_object_id=object_id(@TableName)
EXEC @SQL
END


EXEC DropDependencies 'TableName'

Whenever using EXEC though from constructed strings, ensure you aren't vulnerable to SQL Injection attacks.

Answer 3

Try Out SP_ExecuteSQL or Exec. I see that you are using Exec. Perhaps SP_ExecuteSQL will work?