查看表用量

通过游标和SP_SPACEUSED来查看当前库所有表数据行、已分配空间总量、数据使用总量、索引使用总量、已分配但未使用总量

 1 if OBJECT_ID ('tempdb..#temp') is not null
 2 drop table #temp
 3 go
 4 CREATE TABLE #temp
 5 (
 6 name VARCHAR (100),
 7 rows INT ,
 8 reserved varchar (20),
 9 data varchar (20),
10 index_size varchar (20),
11 unused varchar (20),
12 time datetime default getdate()
13 
14 )
15 DECLARE @tablename VARCHAR( 100)
16 DECLARE @sql VARCHAR( 500)
17 DECLARE @str VARCHAR( 100)
18 DECLARE tablecursor CURSOR
19 FOR
20 SELECT name
21 FROM sys.tables
22 WHERE type_desc = 'USER_TABLE'
23 OPEN tablecursor
24 FETCH NEXT FROM tablecursor INTO @tablename
25 WHILE @@fetch_status = 0
26 BEGIN
27 SET @str = @tablename
28 SET @sql = 'insert into #temp(name,rows,reserved,data,index_size,unused) EXEC sp_spaceused [' + @tablename + ']'
29 EXEC(@sql )
30 FETCH NEXT FROM tablecursor INTO @tablename
31 END
32 CLOSE tablecursor
33 DEALLOCATE tablecursor
34 
35 
36 SELECT * FROM #temp 
37 where rows <> 0
38 order by [rows] desc
39 
40