set nocount on
declare @db varchar(20)
set @db=db_name()
dbcc updateusage(@db) with no_infomsgs
go
create table #tblspace
(数据表名称 varchar(50) null,
数据行数 int null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
末使用空间 varchar(15) null
)
declare @tblname varchar(50)
declare @db varchar(20)
set @db=db_name()
dbcc updateusage(@db) with no_infomsgs
go
create table #tblspace
(数据表名称 varchar(50) null,
数据行数 int null,
保留空间 varchar(15) null,
数据使用空间 varchar(15) null,
索引使用空间 varchar(15) null,
末使用空间 varchar(15) null
)
declare @tblname varchar(50)
declare curtbls cursor for
select table_name from information_schema.tables
where table_type='base table'
select table_name from information_schema.tables
where table_type='base table'
open curtbls
fetch next from curtbls into @tblname
fetch next from curtbls into @tblname
while @@fetch_status=0
begin
insert #tblspace exec sp_spaceused @tblname
fetch next from curtbls into @tblname
end
close curtbls
deallocate curtbls
begin
insert #tblspace exec sp_spaceused @tblname
fetch next from curtbls into @tblname
end
close curtbls
deallocate curtbls
select * from #tblspace order by
convert(int, left(保留空间,len(保留空间)-2)) desc
drop table #tblspace
convert(int, left(保留空间,len(保留空间)-2)) desc
drop table #tblspace
--------------------------
数据表名称 数据行数 保留空间 数据使用空间 索引使用空间 末使用空间
-------------------------------------------------- ----------- --------------- --------------- --------------- ---------------
tblmntquote 528000 34440 KB 34384 KB 8 KB 48 KB
test5 528000 23688 KB 23664 KB 8 KB 16 KB
tb2038 1569 584 KB 520 KB 8 KB 56 KB
tb_1001 1680 520 KB 496 KB 8 KB 16 KB
222 61 24 KB 16 KB 8 KB 0 KB
Customers 19 24 KB 8 KB 16 KB 0 KB
Products 7 24 KB 8 KB 16 KB 0 KB
Sales 32 24 KB 8 KB 16 KB 0 KB
Employee 3 16 KB 8 KB 8 KB 0 KB
Expenses 14 16 KB 8 KB 8 KB 0 KB
aa 3 16 KB 8 KB 8 KB 0 KB
bb 3 16 KB 8 KB 8 KB 0 KB
test 1 16 KB 8 KB 8 KB 0 KB
test2 7 16 KB 8 KB 8 KB 0 KB
testt 6 16 KB 8 KB 8 KB 0 KB
test6 0 0 KB 0 KB 0 KB 0 KB
dtproperties 0 0 KB 0 KB 0 KB 0 KB
-------------------------------------------------- ----------- --------------- --------------- --------------- ---------------
tblmntquote 528000 34440 KB 34384 KB 8 KB 48 KB
test5 528000 23688 KB 23664 KB 8 KB 16 KB
tb2038 1569 584 KB 520 KB 8 KB 56 KB
tb_1001 1680 520 KB 496 KB 8 KB 16 KB
222 61 24 KB 16 KB 8 KB 0 KB
Customers 19 24 KB 8 KB 16 KB 0 KB
Products 7 24 KB 8 KB 16 KB 0 KB
Sales 32 24 KB 8 KB 16 KB 0 KB
Employee 3 16 KB 8 KB 8 KB 0 KB
Expenses 14 16 KB 8 KB 8 KB 0 KB
aa 3 16 KB 8 KB 8 KB 0 KB
bb 3 16 KB 8 KB 8 KB 0 KB
test 1 16 KB 8 KB 8 KB 0 KB
test2 7 16 KB 8 KB 8 KB 0 KB
testt 6 16 KB 8 KB 8 KB 0 KB
test6 0 0 KB 0 KB 0 KB 0 KB
dtproperties 0 0 KB 0 KB 0 KB 0 KB
转载于:https://blog.51cto.com/boyi55/44024