如何获取数据库中含ModifiedTimeStamp列的表年度总行数统计

Got it, let's work through this problem together. You need to generate a dataset that shows row counts grouped by year, but only for tables in the Adventures2008 database that have a [ModifiedTimeStamp] column. Dynamic SQL is the perfect fit here since we need to automatically iterate over all qualifying tables.

Here's a complete, tested solution:

USE Adventures2008;
GO

DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

-- Build SQL logic for each table with the target column
SELECT @DynamicSQL += N'
SELECT 
    ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''' AS TableName,
    YEAR(ModifiedTimeStamp) AS [Year],
    COUNT(*) AS RowCount
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
WHERE ModifiedTimeStamp IS NOT NULL -- Skip rows with missing timestamp
GROUP BY YEAR(ModifiedTimeStamp)
UNION ALL'
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'ModifiedTimeStamp'
  AND t.type = 'U'; -- Only include user-created tables (exclude system tables)

-- Remove the trailing UNION ALL to avoid syntax errors
SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10);

-- Execute the assembled dynamic SQL
EXEC sp_executesql @DynamicSQL;

Key Details & Explanations: #

• Database Context: The USE Adventures2008; line ensures we're working in the correct database from the start.
• System Views: We use sys.tables, sys.schemas, and sys.columns to dynamically find all tables with the ModifiedTimeStamp column. This means you don't have to hardcode table names, and the query will adapt if tables are added or removed later.
• QUOTENAME Function: This wraps schema and table names in square brackets, preventing syntax errors if your tables have special characters (like spaces) or match SQL reserved words.
• NULL Timestamp Handling: The WHERE ModifiedTimeStamp IS NOT NULL clause skips rows with missing timestamps—this keeps your year counts accurate, since YEAR(NULL) returns NULL and would group those rows separately otherwise.
• Dynamic SQL Assembly: We build a single SQL string that unions results from all qualifying tables, then trim off the final UNION ALL to avoid syntax issues before executing.

Quick Notes: #

• If you don't need to exclude rows with NULL timestamps, you can remove the WHERE clause.
• Ensure the account running this query has read access to all target tables and permission to query system views.
• For large databases, this might take a minute to run—try executing it during off-peak hours if performance is a concern.

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