CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed 
(
     TableName sysname
    ,NumRows BIGINT
    ,ReservedSpace VARCHAR(50)
    ,DataSpace VARCHAR(50)
    ,IndexSize VARCHAR(50)
    ,UnusedSpace VARCHAR(50)
) 

insert into #tmp_table_info
select s.name+'.'+t.name 
from sys.tables t 
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';

declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin
    declare @tablename varchar(200)
    select @tablename=tblname
    from #tmp_table_info
    where id =@min

    DECLARE @str VARCHAR(500)
    SET @str =  'sp_spaceused '''+@tablename+''''
    INSERT INTO #SpaceUsed 
    EXEC (@str)  
    set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed

-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''

INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1

SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC

我的文章只与SQL Server 2000相关，并且已经过测试，可以在我的环境中工作。

该代码访问单个实例的所有可能的数据库，而不仅仅是单个数据库。

我使用两个临时表来帮助收集适当的数据，然后将结果转储到一个“Live”表中。

返回的数据是：DatabaseName、DatabaseTableName、Rows（在表中）、data（表的大小，以KB为单位）、entry data（我发现这对了解我上次运行脚本的时间很有用）。

这段代码的缺点是“data”字段不是以int形式存储的（字符“KB”保留在该字段中），这对于排序是有用的（但不是完全必要的）。

希望这段代码能帮助一些人，并为他们节省一些时间！

CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]

AS
BEGIN
   SET NOCOUNT OFF

   CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
   CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

   DECLARE @SQL nvarchar(4000)
   SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '

   INSERT INTO #DatabaseTables(DbName, TableName)
      EXECUTE sp_msforeachdb @Command1=@SQL

   DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR   
   SELECT TableName FROM #DatabaseTables

   DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR   
   SELECT DBName FROM #DatabaseTables

   DECLARE @DBName sysname  
   OPEN AllDatabaseNames  

   DECLARE @TName sysname
   OPEN AllDatabaseTables  

   WHILE 1=1 BEGIN 
      FETCH NEXT FROM AllDatabaseNames INTO @DBName  
      FETCH NEXT FROM AllDatabaseTables INTO @TName 
      IF @@FETCH_STATUS<>0 BREAK  
      INSERT INTO #AllDatabaseTableSizes
         EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName) 

   END 

   --http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
   INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
      SELECT   [dbname], name, [rows],  data FROM #DatabaseTables
      INNER JOIN #AllDatabaseTableSizes
      ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
      GROUP BY [dbname] , name, [rows],  data
      ORDER BY [dbname]
   --To be honest, I have no idea what exact duplicates we are dropping
    -- but in my case a near enough approach has been good enough.
   DELETE FROM [rsp_DatabaseTableSizes]
   WHERE name IN 
      ( 
      SELECT name 
      FROM [rsp_DatabaseTableSizes]
      GROUP BY name
      HAVING COUNT(*) > 1
      )

   DROP TABLE #DatabaseTables
   DROP TABLE #AllDatabaseTableSizes

   CLOSE AllDatabaseTables  
   DEALLOCATE AllDatabaseTables  

   CLOSE AllDatabaseNames  
   DEALLOCATE AllDatabaseNames      
END

--EXEC [dbo].[usp_getAllDBTableSizes] 

如果您需要知道，rsp_DatabaseTableSizes表是通过以下方式创建的：

CREATE TABLE [dbo].[rsp_DatabaseSizes](
    [DatabaseName] [varchar](1000) NULL,
    [dbSize] [decimal](15, 2) NULL,
    [DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]

GO

sp_spaceused可以获取表、索引视图或整个数据库使用的磁盘空间的信息。

例如：

USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

这将报告ContactInfo表的磁盘使用情况信息。

要同时对所有表使用此选项：

USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

您还可以从SQL Server的右键单击“标准报告”功能中获取磁盘使用情况。要获取此报告，请从对象资源管理器中的服务器对象导航，向下移动到数据库对象，然后右键单击任何数据库。从出现的菜单中，选择“报告”，然后选择“标准报告”，再选择“磁盘分区使用情况：[DatabaseName]”。

这将为您提供每个表的大小和记录计数。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)  
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
END

以下是通过以下步骤快速获取所有表格大小的方法：

编写给定的T-SQL命令以列出所有数据库表：从INFORMATION_SCHEMA.TABLES中选择“exec sp_spaceused”+TABLE_NAME，其中TABLE_TYPE=“BASE TABLE”现在复制数据库表列表，并将其复制到新的查询分析器窗口中执行sp_spaceused表1执行sp_spaceused表2执行sp_spaceused表3执行sp_spaceused表4执行sp_spaceused表5在SQL查询分析器中，从顶部工具栏中选择“结果到文件”选项（Ctrl+Shift+F）。现在，最后点击上面工具栏中标记为红色的Execute按钮。所有表的数据库大小现在存储在计算机上的文件中。