|
- use testdb
- go
- if object_id('tempdb.dbo.#tablespaceinfo','U') is not null
- drop table #tablespaceinfo
- create table #tablespaceinfo (
- nameinfo varchar(555),
- rowsinfo bigint,
- reserved varchar(255),
- datainfo varchar(255),
- index_size varchar(255),
- unused varchar(255)
- )
-
- DECLARE @tablename varchar(255);
-
- DECLARE Info_cursor CURSOR FOR
- SELECT [name] FROM sys.tables WHERE type='U';
-
- OPEN Info_cursor
- FETCH NEXT FROM Info_cursor INTO @tablename
-
- WHILE @@FETCH_STATUS = 0
- BEGIN
- insert into #tablespaceinfo exec sp_spaceused @tablename
- FETCH NEXT FROM Info_cursor
- INTO @tablename
- END
-
- CLOSE Info_cursor
- DEALLOCATE Info_cursor
-
- if object_id('tempdb.dbo.#tab','U') is not null
- drop table #tab
- SELECT
- nameinfo
- ,rowsinfo
- ,cast(replace(reserved,' KB','') as bigint)/1024 "reserved(MB)"
- ,cast(replace(datainfo,' KB','') as bigint)/1024 "datainfo(MB)"
- ,cast(replace(index_size,' KB','') as bigint)/1024 "index_size(MB)"
- ,cast(replace(unused,' KB','') as bigint)/1024 "unused(MB)"
- into #tab
- FROM #tablespaceinfo
- ORDER BY Cast(Replace(reserved,'KB','') as INT) DESC
复制代码 |
|