使用sp_spaceused显示MSSQL库表明细(行数、使用的磁盘空间等)

--建临时表
IF OBJECT_ID('tempdb..#used_temp') IS NOT NULL drop table #used_temp
Create TABLE #used_temp(
[name] [nchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[rows] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[reserved] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[data] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[index_size] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL,
[unused] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

--将库表数据大小导入临时表
declare @tname varchar(200)
DECLARE tablename CURSOR FOR 
Select name FROM sysobjects where type='U'
OPEN tablename  
FETCH NEXT FROM tablename INTO @tname
    WHILE @@FETCH_STATUS = 0
BEGIN
insert into #used_temp exec sp_spaceused @tname
    FETCH NEXT FROM tablename INTO @tname   
END 
CLOSE tablename  
DEALLOCATE tablename

--查询
select * from #used_temp