不积跬步,无以至千里;不积小流,无以成江海。

Dean's blog

  • Join Us on Facebook!
  • Follow Us on Twitter!
  • LinkedIn
  • Subcribe to Our RSS Feed

获取SQL所有表记录数、空间大小

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

 

执行如果如下:

不允许评论
粤ICP备17049187号-1