查询最大数据表

你知道你的SQL Server所有库里面哪个表最大吗？跑一下这段脚本试试看看...

USE tempdb
GO

IF OBJECT_ID('tempdb..#db', 'U') <> 0 
DROP TABLE #db 

IF OBJECT_ID('tempdb..#tb', 'U') <> 0 
DROP TABLE #tb 

IF OBJECT_ID('tempdb..#dbtable', 'U') <> 0 
DROP TABLE #dbtable 

CREATE TABLE #tb(name varchar(200),rows bigint,reserved varchar(200),data varchar(200),index_size varchar(200),unused varchar(200)); 
CREATE TABLE #dbtable(db varchar(100),name varchar(200),rows bigint,reserved varchar(200),data varchar(200),index_size varchar(200),unused varchar(200)) 
SELECT name, px = ROW_NUMBER() OVER(ORDER BY(SELECT 1)) INTO #db FROM sys.databases WITH(NOLOCK) WHERE state = 0 AND database_id > 4 
-- and name = 'xxx'

DECLARE @sql varchar(max) 
DECLARE @px int 
DECLARE @dbname sysname 
DECLARE @tdbname sysname 
DECLARE @px2 int

SELECT @dbname = name, @px = px FROM #db WHERE px = 1; 
WHILE @@rowcount > 0 
BEGIN 
    SET @sql = 'use ' +QUOTENAME(@dbname)+ CHAR(10) 
             + 'exec sp_msforeachtable ''
               insert into #tb execute sp_spaceused ''''?'''';''
               insert into #dbtable select '''+@dbname+''',* from #tb
               truncate table #tb' 
    EXECUTE(@sql) 
    SELECT TOP(1) @dbname = name, @px = px FROM #db WHERE px > @px; 
END 

SELECT 
   db AS [database_name] 
  ,name AS [table_name] 
  ,rows 
  ,CONVERT(NUMERIC(18,2),CONVERT(NUMERIC(18,2),SUBSTRING(reserved,1,LEN(reserved)-2))/1024) AS reserved_size_MB 
  ,CONVERT(NUMERIC(18,2),CONVERT(NUMERIC(18,2),SUBSTRING(data,1,LEN(data)-2))/1024) AS data_size_MB 
  ,CONVERT(NUMERIC(18,2),CONVERT(NUMERIC(18,2),SUBSTRING(index_size,1,LEN(index_size)-2))/1024) AS index_size_MB 
  ,CONVERT(NUMERIC(18,2),CONVERT(NUMERIC(18,2),SUBSTRING(unused,1,LEN(unused)-2))/1024) AS unused_size_MB 
FROM #dbtable 
--WHERE rows <> '0' 
ORDER BY CONVERT(BIGINT,LEFT(reserved,LEN(reserved)-2)) DESC

执行结果大概如下：