Question

用SQL如何批量删除具有相同前缀的表sql批量删除表?

如题,尽量不要使用存储过程或触发器!drop table where like '前缀_%' 貌似失败!求助.

Answer 1

你的思路我也不会写,但是你可以用导出工具把要保留的表导出来,然后清空数据库,再导回去.
(导出的时候根据数据库的不同,导出工具可能也不同,但是让表按名称排序应该是很简单的,所以也应该很容易把不要的表过滤出来)
仅供参考.

Answer 2

alter proc DeleteSingleTable(@tablename varchar(100))
as
begin
declare @SQL varchar(2000)
declare @constraintName varchar(100)

declare curName cursor for
select name from sysobjects
where xtype = 'f ' and parent_obj =
(select [id] from sysobjects where [name]=@tablename and xtype = 'u ')

open curName
fetch next from curName into @constraintName
while @@fetch_status = 0
begin
set @SQL = 'alter table ' + @tablename + ' drop constraint '
set @SQL = @SQL + @constraintName
exec(@SQL)
fetch next from curName into @constraintName
end
close curName
deallocate curName
end