T-SQL dropping multiple tables

Question

I'm using a T-SQL script to remove all constraints and all tables in my database to give me a clean slate upon execution, it seems to work fine for constraints, but when adapting the segment to clear my tables out it's giving me an error message, code is as follows:

declare @tableCommand varchar(255) 
declare tableCursor cursor for
    select 'drop table '+ t.name
    from sys.tables t
    where t.type = 'U'

open tableCursor 
fetch next from tableCursor into @tableCommand 

while @@fetch_status = 0 begin
    exec @tableCommand
    fetch next from tableCursor into @tableCommand
end 

close tableCursor 
deallocate tableCursor

As I say, a similar segment of code works fine to drop my constraints, but with this code I just get the error:

Msg 2812, Level 16, State 62, Line 15
Could not find stored procedure 'drop table calendar'.

Any ideas?

Answer 1

I think you miss some brackets:

while @@fetch_status = 0 begin
    exec (@tableCommand);
    fetch next from tableCursor into @tableCommand
end 

Answer 2

I won't use CURSOR to do this.

SELECT ' Drop table ' + s.NAME + '.' + t.NAME
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U' 

Exec sp_executesql @sql

Note: If you have any foreign Keys defined between tables then you have to disable it before dropping tables. Also if there is any schema bound objects then again you cannot drop tables.

Run the below query to disable all foreign keys present in your DB.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

for more info check here

Answer 3

T-SQL EXEC is for calling stored procedure.

To execute dynamic SQL, you can use the sp_executesql procedure, e.g.

EXEC sp_executesql @tablecommand

References:

sp_executesql https://msdn.microsoft.com/en-us/library/ms188001.aspx

EXECUTE https://msdn.microsoft.com/en-us/library/ms188332.aspx