How to execute a dynamically created sql statement

Question

I wish to drop all the constraints of tables I have. Since this is not possible in SQL Server, I've created a script (based on something found online) that is creating dynamically the drop statements as a result of a Select:

SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TABLE_NAME LIKE 'Old_%''

This done, I can't find how to parse it and execute that in a loop.

My attempt is to create a cursor like this, but I have no clue then to make it executing each line:

DECLARE @AlterTables nvarchar(2000)
SET @AlterTables = 'DECLARE Dyn_cursor CURSOR
FOR SELECT ''ALTER TABLE '' + TABLE_SCHEMA + ''.['' + TABLE_NAME + ''] DROP CONSTRAINT ['' + CONSTRAINT_NAME + '']''
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ''FOREIGN KEY''
AND TABLE_NAME LIKE ''Old_%'''
Exec(@AlterTables)

Open Dyn_Cursor 
    FETCH NEXT FROM Dyn_Cursor INTO @name   
    WHILE @@FETCH_STATUS = 0   
BEGIN 
END

Close Dyn_cursor
Deallocate Dyn_cursor

Thank you in advance to whom would have a solution for that!

cheers

J.

Goul · Accepted Answer · 2011-03-03 04:26:47Z

2

Thank you jzd, however it wasn't working the way you recommended. I think it is required to declare the selection you are working on before opening the cursor. In order to do so, I've updated the code like this (that I've found on the web):

declare @str varchar(max)
declare cur cursor for
    SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TABLE_NAME LIKE 'Old_%'
    open cur
        FETCH NEXT FROM cur INTO @str
        WHILE (@@fetch_status = 0)
            BEGIN
                EXEC (@str)
                FETCH NEXT FROM cur INTO @str
            END
    close cur
deallocate cur

answered Mar 3, 2011 at 4:26

Goul

5832 gold badges5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jzd · Accepted Answer · 2011-02-23 12:20:23Z

0

You are close. Your cursor needs to not be dynamic and needs to just select the table constraints. Then you need to execute the update inside the loop, take your code here:

Open Dyn_Cursor 
    FETCH NEXT FROM Dyn_Cursor INTO @name   
    WHILE @@FETCH_STATUS = 0   
BEGIN 
--Add Here
END

And add in something like this between the Begin and End statements:

SET @AlterTable = 'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] 
DROP CONSTRAINT ['' + @name + '']'
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = ''FOREIGN KEY''
AND TABLE_NAME LIKE ''Old_%'''
Exec(@AlterTable )
FETCH NEXT FROM Dyn_Cursor INTO @name

answered Feb 23, 2011 at 12:20

jzd

23.6k9 gold badges58 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to execute a dynamically created sql statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related