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
