22,209
社区成员
发帖
与我相关
我的任务
分享
create table #tb(表名 sysname,记录数 int ,保留空间 varchar(10),使用空间 varchar(10),索引使用空间 varchar(10),未用空间 varchar(10))
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select 表名 from #tb where 记录数 =0
go
drop table #tb
declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)
/*
name num
----------- -----------
titleauthor 25
stores 6
sales 21
roysched 86
discounts 3
jobs 14
pub_info 8
employee 43
authors 23
publishers 8
titles 18
*/
declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a where num = 0'
exec(@sql)
/*
name num
----------- -----------
*/