SQL Server查询所有表统计信息返回空结果，求排查原因

Hey David, let's dig into why your query isn't returning any results. The core issue is easy to spot, but I'll cover all possible causes and fixes to get you sorted:

The critical mistake is in your WHERE clause:

WHERE s.object_id = OBJECT_ID('@name');

By wrapping @name in single quotes, you're treating it as a literal string instead of referencing the table name variable from your cursor. SQL Server is looking for a table literally named @name (which doesn't exist), hence empty results every time.

Quick Fix #

Remove the single quotes to use the variable directly:

WHERE s.object_id = OBJECT_ID(@name);

For better reliability (to avoid conflicts with tables in non-default schemas), you can explicitly include the schema name (most often dbo):

WHERE s.object_id = OBJECT_ID('dbo.' + @name);

• Insufficient Permissions: If your database account lacks VIEW DEFINITION permissions or isn't part of a role that can access system catalog views (like sys.stats, sys.stats_columns), the query will return empty results. Verify your account has the necessary access.
• No Statistics Exist for Tables: While SQL Server auto-creates statistics by default, brand-new tables that haven't been queried yet, or tables where all stats were manually deleted, will have no data to return. Test this by creating a sample statistic on a table:

  CREATE STATISTICS sample_stats ON dbo.YourTableName(YourColumnName);
  

• Empty Cursor to Begin With: Run SELECT name FROM sys.tables on its own. If this returns no rows, your database has no user tables, so the cursor loop never executes at all.

Here's the polished, fixed version of your code—with proper variable handling, schema references, and cleanup for the cursor:

DECLARE @name VARCHAR(50);
DECLARE @fullTableName VARCHAR(100); -- Store schema-qualified table name
DECLARE db_cursor CURSOR FOR 
    SELECT name FROM sys.tables;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @fullTableName = 'dbo.' + @name; -- Adjust schema if needed
    SELECT 
        s.name AS statistics_name,
        c.name AS column_name,
        sc.stats_column_id
    FROM sys.stats AS s
    INNER JOIN sys.stats_columns AS sc 
        ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
    INNER JOIN sys.columns AS c 
        ON sc.object_id = c.object_id AND c.column_id = sc.column_id
    WHERE s.object_id = OBJECT_ID(@fullTableName);

    FETCH NEXT FROM db_cursor INTO @name;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor; -- Always clean up cursors!

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