常用脚本--查看数据库中各表的行数和大小
--====================================== --查看各表的行数 --is_ms_shipped=0代表用户创建的表 --(I.indid=1 OR I.indid=0)代表聚簇索引和堆表 SELECT T.name AS TableName, I.rows AS RowsCount, (I.dpages*8/1024) AS DataSpaceMB, ((I.used-I.dpages)*8/1024) AS IndexSpaceMB, (I.used*8/1024) AS TotalSpaceMB FROM sys.sysindexes I INNER JOIN sys.tables T ON I.id=T.object_id and (I.indid=1 OR I.indid=0) AND T.is_ms_shipped=0 --====================================== --使用EXEC sys.sp_spaceused @tbname来获取表的大小和行数 IF(OBJECT_ID('tempdb.dbo.#tablespaceinfo') IS NOT NULL) BEGIN DROP TABLE #tablespaceinfo PRINT 'DROP TABLE #tablespaceinfo' END CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(50), rowsinfo INT, reserved VARCHAR(20), datainfo VARCHAR(20), index_size VARCHAR(20), unused VARCHAR(20) ); DECLARE @tablename VARCHAR(255); --表名称 DECLARE @cmdsql VARCHAR(500); DECLARE Info_cursor CURSOR FOR SELECT T.name FROM sys.tables T WHERE is_ms_shipped=0; OPEN Info_cursor; FETCH NEXT FROM Info_cursor INTO @tablename; WHILE (@@FETCH_STATUS = 0) BEGIN execute sp_executesql N'INSERT INTO #tablespaceinfo EXEC sys.sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename FETCH NEXT FROM Info_cursor INTO @tablename END CLOSE Info_cursor DEALLOCATE Info_cursor SELECT * FROM #tablespaceinfo --====================================== --使用sys.dm_db_partition_stats查看各表的行数 --====================================== --使用sys.dm_db_index_physical_stats查看各表的行数 --dm_db_index_physical_stats会导致锁表,请谨慎使用。 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'DB1'), OBJECT_ID(N'dbo.TB22'), NULL, NULL , 'DETAILED');
另外一种方式,首先生成脚本
--====================================== --使用EXEC sys.sp_spaceused 来获取表的大小和行数 IF(OBJECT_ID('tempdb.dbo.#tablespaceinfo') IS NOT NULL) BEGIN DROP TABLE #tablespaceinfo PRINT 'DROP TABLE #tablespaceinfo' END CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(50), rowsinfo INT, reserved VARCHAR(20), datainfo VARCHAR(20), index_size VARCHAR(20), unused VARCHAR(20) ); GO SELECT N'INSERT INTO #tablespaceinfo EXEC sys.sp_spaceused ''['+SCHEMA_NAME(T.schema_id)+'].['+T.name+']''' FROM sys.tables T
在执行一遍SELECT 出来的脚本