[MSSQL]获取SQL Server数据库里表的占用容量大小
CREATE PROCEDURE get_tableinfo ASif not exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) create tabletablespaceinfo --创建结果存储表 (nameinfo varchar(50) ,rowsinfo int , reserved varchar(20) ,datainfo varchar(20),index_size varchar(20) ,unused varchar(20) )delete from tablespaceinfo --清空数据表declare @tablename varchar(255)--表名称declare @cmdsql varchar(500)DECLARE Info_cursor CURSOR FORselect o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1and o.name not like N'#%%'order by o.nameOPEN Info_cursorFETCH NEXT FROM Info_cursorINTO @tablename WHILE @@FETCH_STATUS = 0 BEGINif exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1) execute sp_executesqlN'insert into tablespaceinfoexec sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablenameFETCH NEXT FROM Info_cursorINTO @tablenameENDCLOSE Info_cursor DEALLOCATE Info_cursor GO执行存储过程 exec get_tableinfo查询运行该存储过程后得到的结果 select * from tablespaceinfoorder by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
页:
[1]