使用SP_MSFOREACHDB与sp_ineachdb仅返回少量结果的问题排查

问题现象 #

使用SP_MSFOREACHDB或sp_ineachdb批量查询全库标识列时，仅返回50条（SP_MSFOREACHDB）和48条（sp_ineachdb）结果，远低于预期的数百条（单个数据库就有300+条标识列）。但单独针对每个数据库执行查询代码时，能得到完整结果。

原执行代码 #

IF OBJECT_ID('tempdb..#identity_columns') IS NOT NULL DROP TABLE #identity_columns
GO

CREATE TABLE #identity_columns
( [database_name] SYSNAME NOT NULL,
[schema_name] SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
column_name SYSNAME NOT NULL,
[type_name] SYSNAME NOT NULL,
maximum_identity_value BIGINT NOT NULL,
current_identity_value BIGINT NULL,
percent_consumed DECIMAL(25,4) NULL );

DECLARE @Table_Name NVARCHAR(MAX);
DECLARE @Schema_Name NVARCHAR(MAX)
DECLARE @command varchar(1000);

SELECT @command= '
INSERT INTO #identity_columns
([database_name], [schema_name], table_name, column_name, [type_name], maximum_identity_value, current_identity_value)
SELECT
DB_NAME() AS database_name,
''' + schemas.name + ''' AS schema_name,
''' + tables.name + ''' AS table_name,
''' + columns.name + ''' AS column_name,
''' + types.name + ''' AS type_name,
CASE
WHEN ''' + types.name + ''' = ''TINYINT'' THEN CAST(255 AS BIGINT)
WHEN ''' + types.name + ''' = ''SMALLINT'' THEN CAST(32767 AS BIGINT)
WHEN ''' + types.name + ''' = ''INT'' THEN CAST(2147483647 AS BIGINT)
WHEN ''' + types.name + ''' = ''BIGINT'' THEN CAST(9223372036854775807 AS BIGINT)
WHEN ''' + types.name + ''' IN (''DECIMAL'', ''NUMERIC'') THEN CAST(REPLICATE(9, (' + CAST(columns.precision AS VARCHAR(MAX)) + ' - ' + CAST(columns.scale AS VARCHAR(MAX)) + ')) AS BIGINT)
ELSE -1
END AS maximum_identity_value,
IDENT_CURRENT(''[' + schemas.name + '].[' + tables.name + ']'') AS current_identity_value;
'
FROM sys.tables
INNER JOIN sys.columns
ON tables.object_id = columns.object_id
INNER JOIN sys.types
ON types.user_type_id = columns.user_type_id
INNER JOIN sys.schemas
ON schemas.schema_id = tables.schema_id
WHERE columns.is_identity = 1;

EXEC dbo.sp_ineachdb @command
UPDATE #identity_columns
SET percent_consumed = CAST(CAST(current_identity_value AS DECIMAL(25,4)) / CAST(maximum_identity_value AS DECIMAL(25,4)) AS DECIMAL(25,2)) * 100;
select * from #identity_columns order by percent_consumed desc

问题原因 #

1. SQL拼接逻辑错误：当前代码是在执行脚本的当前数据库中查询sys.tables等系统视图，将当前库的标识列对象名硬编码拼接进@command，然后用sp_ineachdb在每个库执行这段硬编码的SQL。这导致每个库执行时，都是插入当前库的标识列数据，而不是查询自身的标识列，最终结果只是当前库数据的重复插入，数量自然远低于预期。
2. 字符串长度限制：@command定义为varchar(1000)，如果拼接后的SQL语句长度超过1000字符，会被自动截断，导致部分SQL无法执行，进一步减少返回结果。

修正后的代码 #

将@command改为动态SQL，让其在每个目标数据库中独立查询自身的系统视图，而不是提前硬编码对象名：

IF OBJECT_ID('tempdb..#identity_columns') IS NOT NULL DROP TABLE #identity_columns
GO

CREATE TABLE #identity_columns
( [database_name] SYSNAME NOT NULL,
[schema_name] SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
column_name SYSNAME NOT NULL,
[type_name] SYSNAME NOT NULL,
maximum_identity_value BIGINT NOT NULL,
current_identity_value BIGINT NULL,
percent_consumed DECIMAL(25,4) NULL );

DECLARE @command NVARCHAR(MAX); -- 改用NVARCHAR(MAX)避免长度截断

SET @command = N'
INSERT INTO #identity_columns
([database_name], [schema_name], table_name, column_name, [type_name], maximum_identity_value, current_identity_value)
SELECT
    DB_NAME() AS database_name,
    s.name AS schema_name,
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS type_name,
    CASE
        WHEN ty.name = N''TINYINT'' THEN CAST(255 AS BIGINT)
        WHEN ty.name = N''SMALLINT'' THEN CAST(32767 AS BIGINT)
        WHEN ty.name = N''INT'' THEN CAST(2147483647 AS BIGINT)
        WHEN ty.name = N''BIGINT'' THEN CAST(9223372036854775807 AS BIGINT)
        WHEN ty.name IN (N''DECIMAL'', N''NUMERIC'') THEN CAST(REPLICATE(N''9'', (c.precision - c.scale)) AS BIGINT)
        ELSE -1
    END AS maximum_identity_value,
    IDENT_CURRENT(QUOTENAME(s.name) + N''.'' + QUOTENAME(t.name)) AS current_identity_value
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types ty ON ty.user_type_id = c.user_type_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.is_identity = 1;
';

EXEC dbo.sp_ineachdb @command;

UPDATE #identity_columns
SET percent_consumed = CAST(CAST(current_identity_value AS DECIMAL(25,4)) / CAST(maximum_identity_value AS DECIMAL(25,4)) AS DECIMAL(25,2)) * 100;

SELECT * FROM #identity_columns ORDER BY percent_consumed DESC;

修正说明 #

• 将@command改为NVARCHAR(MAX)，避免SQL语句过长被截断。
• 去掉了原代码中提前拼接对象名的逻辑，改为在每个目标库中直接查询sys.tables等系统视图，确保每个库返回自身的标识列数据。
• 使用QUOTENAME函数处理对象名，避免因特殊字符导致的SQL语法错误。

内容的提问来源于stack exchange，提问作者Maria