Delete all tables from a SQL database using Transact SQL Cursors

If you have a SQL2016 database but only SQL 2008R2 SSMS then you can’t delete tables with extreme prejudice using the DEL key, you get an error something like “index out of range”.

However this SQL will do the same job.

Just remember, it’s your foot…

From an idea from Richard…

declare @sql nvarchar(2048)
declare @tn nvarchar(200)

declare tablecursor cursor
for
select name from sys.tables
open tablecursor
fetch next from tablecursor into @tn
while @@FETCH_STATUS=0
begin
print @tn
declare fkcursor cursor
for
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(@tn)
open fkcursor
fetch next from fkcursor into @sql
while @@fetch_status = 0
begin
print @sql
exec(@sql)
fetch next from fkcursor into @sql
end
close fkcursor;
deallocate fkcursor

set @sql = ‘drop table ‘ + @tn
exec(@sql)

fetch next from tablecursor into @tn
end

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Website by Daneswood