怎么删除数据库中的表？谢谢回答(100分送上)

[西村秀木]

注意：是有一个数据库，里面有200张表，大约100张表是多余的。每次使用数据库的时候都觉得费劲，现在想删除这些表。表的特征是以dbo为架构的的，后面开头从c到x是多余的。表名是dbo.caaaa,dbo.cadf……dbo.xkkk,dbo.ylll。我现在只想删掉c到k之间的。
同事说可以用sql 语句来做。是可以drop table ***，但是不能获取到所有的表名让我很郁闷。哪位大神能教下怎么获取所有表名(带架构前缀名的)也行。多谢！

[發糞塗牆]

SELECT 'drop table '+name FROM sys.tables WHERE name LIKE '%[cx]%'

[阳泉酒家小当家]

select 'drop table dbo.' + t.name + ';'
from sys.tables t
where t.name like '[c-k]%'
order by t.name

[發糞塗牆]

SELECT 'drop table '+name FROM sys.tables WHERE name LIKE '%[ck]%'

[阳泉酒家小当家]

生成删除语句，改一下：

select 'drop table dbo.' + t.name + ';'
from sys.tables t
inner join sys.schemas s
        on s.schema_id = t.schema_id
where t.name like '[c-k]%' --表的名称的开始字母,从c到k的
      and s.name = 'dbo'   --架构为dbo的
order by t.name

[發糞塗牆]

SELECT  'drop table '+o.name +'  ;'
FROM sys.objects o INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.type='U'AND o.name LIKE '%[ck]%' AND s.name='dbo'

[發糞塗牆]

测了一下，like 那里c前面不加%很多数据出不来

[Andy__Huang]

select 'drop table ['+name+']; ' 
from sys.tables 
where left(name,1) between 'c' and 'k'

[唐诗三百首]

-- 获取所有待删除的表名(带架构前缀名的)
select b.name+'.'+a.name 'tablename' 
 from sys.tables a
 inner join sys.schemas b on a.schema_id=b.schema_id
 where b.name='dbo' and left(a.name,1) between 'c' and 'k'


-- 执行删除
declare @tsql varchar(500),@tn varchar(100)
declare ap scroll cursor for
 select b.name+'.'+a.name 'tablename'
 from sys.tables a
 inner join sys.schemas b on a.schema_id=b.schema_id
 where b.name='dbo' and left(a.name,1) between 'c' and 'k'

open ap
fetch first from ap into @tn

while(@@fetch_status<>-1)
begin
 select @tsql='drop table '+@tn
 exec(@tsql)
 fetch next from ap into @tn
end

close ap
deallocate ap

[雄牛]

先输出语句： SELECT 'drop table '+name FROM sys.tables WHERE name LIKE '%[ck]%' 再将结果复制出来，执行．．

[-Tracy-McGrady-]

select 'drop table ['+name+']; ' from sys.tables where left(name,1) between 'c' and 'k'

[sdhp]

exec sp_MSforeachtable @command1="drop table ?",@whereand="and name like '[c-k]%'"

[duangufei]

我觉得还是右键删除的比较好 数据库表明不都有表明排序么 直接shift到k那点 多方便

[houyajin]

可以drop table 1,2的，你把表名查找出来。批量删除吧。

[最爱午夜]

USE databaseName GO DECLARE @NAME VARCHAR(100) DECLARE @SQL VARCHAR(MAX) DECLARE CUR CURSOR FOR SELECT '['+B.[name]+'].['+A.[name]+']' FROM sys.objects AS A JOIN sys.schemas AS B ON A.[schema_id]=B.[schema_id] WHERE type_desc='USER_TABLE' AND A.[name] LIKE 'MU_%' OPEN CUR FETCH CUR INTO @NAME WHILE @@FETCH_STATUS=0 BEGIN SET @SQL=N'DROP TABLE '+@NAME EXEC(@SQL) FETCH CUR INTO @NAME END CLOSE CUR DEALLOCATE CUR