试试如下语句吧:
1
create
table
#tb(表名 sysname,记录数
int
2 ,保留空间 varchar ( 10 ),使用空间 varchar ( 10 )
3 ,索引使用空间 varchar ( 10 ),未用空间 varchar ( 10 ))
4
5 insert into #tb exec sp_MSForEachTable ' EXEC sp_spaceused '' ? '''
6
7 select * from #tb
2 ,保留空间 varchar ( 10 ),使用空间 varchar ( 10 )
3 ,索引使用空间 varchar ( 10 ),未用空间 varchar ( 10 ))
4
5 insert into #tb exec sp_MSForEachTable ' EXEC sp_spaceused '' ? '''
6
7 select * from #tb
上面得到的#tb只有表名,而不包括表的schema的名字,所以也许你还需要下面的另一张临时表:
1
select
*
2 into #ta
3 from
4 (
5 select (S.Name + ' . ' + O.Name) as fullName, O. Object_Id as ID from sys.objects O
6 inner join sys.schemas S on o.schema_id = s.schema_id
7 where O.type = ' U '
8 ) A
2 into #ta
3 from
4 (
5 select (S.Name + ' . ' + O.Name) as fullName, O. Object_Id as ID from sys.objects O
6 inner join sys.schemas S on o.schema_id = s.schema_id
7 where O.type = ' U '
8 ) A