分享：sp_get_table_max 获取实例下表行数最大的表

最新推荐文章于 2021-08-24 15:08:37 发布

最新推荐文章于 2021-08-24 15:08:37 发布 · 96 阅读

本文介绍了一个SQL存储过程示例，该过程用于获取指定数据库中最大表的数据，并提供了一种方法来查找表的最大行数。通过使用动态SQL和临时表，此存储过程能够在任意数据库上运行，帮助用户快速定位到数据量最大的表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE master 
go 
IF object_id('sp_get_table_max','P') IS NOT NULL 
DROP PROCEDURE sp_get_table_max 
  
go 
CREATE PROCEDURE sp_get_table_max ( @db NVARCHAR(128) = '' ,@tb NVARCHAR(128) = '',@top INT = 100 )  
AS 

SET NOCOUNT ON ; 
 
CREATE TABLE #TableSpace   
  ( DB_NAME VARCHAR(128) DEFAULT ( db_name()), 
   TableName VARCHAR(128) , 
   SCHEMA_NAME VARCHAR(128), 
   RowsCount CHAR(32) ) 

DECLARE @sql NVARCHAR(max) 
SET @sql = '' 
SELECT @sql = @sql + REPLACE ( 
' 
USE [@dbname] 
INSERT INTO #TableSpace ( TableName , SCHEMA_NAME, RowsCount )  
SELECT b.name AS tablename ,SCHEMA_NAME(b.schema_id) SCHEMA_NAME, c.row_count AS datacount 
FROM  sys.indexes a , 
    sys.objects b , 
    sys.dm_db_partition_stats c 
WHERE  a.[object_id] = b.[object_id] 
    and b.type = ''U'' and b.name like '''+ @tb + '%''
    AND b.[object_id] = c.[object_id] 
    AND a.index_id = c.index_id 
    AND a.index_id < 2 
    AND b.is_ms_shipped = 0 
' ,'@dbname',name)  
FROM sys.databases 
WHERE name LIKE @db + '%' AND database_id >= 5 
PRINT @sql 
EXEC (@sql) 

SELECT TOP (@top) ' SELECT TOP 10 * FROM ['+ DB_NAME +'].['+ SCHEMA_NAME +'].['+TableName+ ']' AS SQL ,* 
FROM #TableSpace ORDER BY CAST(RowsCount AS BIGINT) DESC 
DROP TABLE [#TableSpace] 


go 
EXEC sp_MS_marksystemobject 'sp_get_table_max' 
go