这是2008的,前50个大表
SELECT top 50 OBJ.name AS ObjName
,STAT.row_count AS [RowCount]
,sum(STAT.used_page_count )* 8/1024 AS UsedSizeMB
FROM sys.partitions AS PART
INNER JOIN sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN sys.objects AS OBJ
ON STAT.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS INDX
ON STAT.object_id = INDX.object_id
AND STAT.index_id = INDX.index_id
group by OBJ.name ,STAT.row_count
ORDER BY UsedSizeMB desc
SELECT top 50 OBJ.name AS ObjName
,STAT.row_count AS [RowCount]
,sum(STAT.used_page_count )* 8/1024 AS UsedSizeMB
FROM sys.partitions AS PART
INNER JOIN sys.dm_db_partition_stats AS STAT
ON PART.partition_id = STAT.partition_id
AND PART.partition_number = STAT.partition_number
INNER JOIN sys.objects AS OBJ
ON STAT.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.indexes AS INDX
ON STAT.object_id = INDX.object_id
AND STAT.index_id = INDX.index_id
group by OBJ.name ,STAT.row_count
ORDER BY UsedSizeMB desc
go
INSERT INTO test1(name,[rows],reserved,data,index_size,unused)
EXECUTE sp_msforeachtable 'sp_spaceused [?] '
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
) CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
) INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE' OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']',''); -- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1 SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename; DROP TABLE #temp_Table