无需PIVOT/UNPIVOT，利用系统表实现多表动态联合并添加Field与Data列的SQL存储过程改造问询

Got it, let's tackle this problem head-on. You want to create a unified timeline of all user operations across multiple tables, turning each column in every row into a separate entry—without having to manually list every column or use PIVOT/UNPIVOT. Here's how to do this using SQL Server's system catalog views and dynamic SQL, integrated into your stored procedure:

Key Approach #

We'll leverage sys.tables and sys.columns to dynamically generate the "unpivot" logic for each table. Instead of using UNPIVOT, we'll use CROSS APPLY (VALUES (...)) to convert each column into a row, then union all these results together. This way, any new columns added to your tables will automatically be included without updating the procedure.

Complete Stored Procedure #

CREATE PROCEDURE [dbo].[TerminalHistoryTimeline] 
    @Terminal varchar(100), 
    @StartDate datetime2(0), 
    @EndDate datetime2(0) 
AS 
BEGIN 
    SET NOCOUNT ON;

    -- Variable to hold our dynamic SQL query
    DECLARE @DynamicSQL NVARCHAR(MAX);

    -- Build the dynamic SQL by generating an unpivot query for each table
    SELECT @DynamicSQL = STRING_AGG(
        CAST('
            SELECT 
                TableName = ''' + QUOTENAME(t.name) + ''',
                DateTime = t1.CreateDate,
                Field = v.Field,
                Data = v.Data
            FROM ' + QUOTENAME(t.name) + ' t1
            CROSS APPLY (
                VALUES ' + STRING_AGG(
                    '(''' + QUOTENAME(c.name) + ''', CAST(t1.' + QUOTENAME(c.name) + ' AS VARCHAR(MAX)))',
                    ','
                ) + '
            ) v(Field, Data)
            WHERE t1.Terminal = @Terminal 
              AND t1.CreateDate BETWEEN @StartDate AND @EndDate' 
        AS NVARCHAR(MAX)),
        ' UNION ALL '
    )
    FROM sys.tables t
    JOIN sys.columns c ON t.object_id = c.object_id
    -- Filter to only tables that have both Terminal and CreateDate columns
    WHERE EXISTS (
        SELECT 1 FROM sys.columns 
        WHERE object_id = t.object_id AND name = 'Terminal'
    )
    AND EXISTS (
        SELECT 1 FROM sys.columns 
        WHERE object_id = t.object_id AND name = 'CreateDate'
    )
    -- Optional: Exclude system tables if needed
    AND t.is_ms_shipped = 0
    GROUP BY t.object_id, t.name;

    -- Execute the dynamic SQL with parameters to avoid SQL injection
    EXEC sp_executesql 
        @DynamicSQL,
        N'@Terminal varchar(100), @StartDate datetime2(0), @EndDate datetime2(0)',
        @Terminal = @Terminal,
        @StartDate = @StartDate,
        @EndDate = @EndDate;
END

How This Works #

1. System Catalog Queries: We query sys.tables to get all your tables that have both Terminal and CreateDate columns (you can adjust the filter if you only want specific tables).
2. Dynamic Unpivot Logic: For each table, we use STRING_AGG to build a VALUES clause that converts every column into a (Field, Data) pair. We cast all values to VARCHAR(MAX) to ensure compatibility across different data types (since UNION ALL requires matching data types).
3. Parameterized Execution: We use sp_executesql to run the generated query, passing in your parameters safely to avoid SQL injection risks.
4. Automatic Column Inclusion: Any new columns added to your tables will automatically be picked up by the system catalog views, so you don't need to update the procedure when schema changes.

Notes #

• Quoted Identifiers: We use QUOTENAME around table and column names to handle any special characters or reserved words in your schema.
• Performance: If you have large tables, consider adding indexes on Terminal and CreateDate to speed up the filtering. You can add OPTION(RECOMPILE) to the end of each table's query if you notice performance issues with dynamic plan caching.
• Data Type Conversion: Casting to VARCHAR(MAX) ensures all data types are compatible, but you might want to switch to NVARCHAR(MAX) if you have Unicode data in your tables.

This procedure will output exactly the structure you're looking for: each row from every table is split into multiple rows, one for each column, with the table name, timestamp, field name, and field value.

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