IF EXISTS(SELECT 1 FROM tempdb..sysobjects WHERE id=object_id('tempdb..#tabName') and xtype='u')
DROP TABLE #tabName
GO
CREATE TABLE #tabName(
tabname VARCHAR(100),
rowsNum VARCHAR(100),
reserved VARCHAR(100),
data VARCHAR(100),
index_size VARCHAR(100),
unused_size VARCHAR(100)
)
DECLARE @name VARCHAR(100)
DECLARE cur CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='u' ORDER BY name
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@fetch_status=0
BEGIN
INSERT INTO #tabName
EXEC sp_spaceused @name
--print @name
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur
SELECT tabname AS '表名',rowsNum AS '表数据行数',reserved AS '保留大小',data AS '数据大小',index_size AS '索引大小',unused_size AS '未使用大小'
FROM #tabName
--WHERE tabName not like 't%'
ORDER BY CAST(rowsNum AS INT) DESC
执行如果如下：
