declare @cmd varchar(4000)
declare cmds4 cursor for 
Select
    'drop table [' + Table_Name + ']'
From
    INFORMATION_SCHEMA.TABLES
Where
    Table_Name like 'tblacad%'

open cmds4
while 1=1
begin
    fetch cmds4 into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end


CLOSE cmds4
DEALLOCATE cmds4

------解决方案--------------------
SQL code
--删除外键约束
DECLARE c1 cursor for 
    select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
    from sysobjects 
    where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
    begin 
        exec(@c1)
        fetch next from c1 into @c1
    end
close c1
deallocate c1 
--删除表
DECLARE c2 cursor for 
    select 'drop table ['+name +']; '
    from sysobjects 
    where xtype = 'u' 
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
    begin
        exec(@c2)
        fetch next from c2 into @c2
    end
close c2
deallocate c2

------解决方案--------------------
SQL code
exec sp_msforeachtable @command1="drop table ? ;",
                       @whereand='and schema_id = (select schema_id from sys.schemas where [name] like ''%李%'''

------解决方案--------------------
提供思路如下

先用语句取出所有系统表的名称，可以根据李过滤

循环所有出来的表名，逐个drop table

------解决方案--------------------
探讨


引用:

我对这个也不熟，如果你的表么有什么constraint的话
如下代码应该能达到
SQL code


declare @cmd varchar(4000)
declare cmds4 cursor for
Select
'drop table [' + Table_Name + ']'
From
INFORMATION_SCHEMA.TABLE……