如何在SQL Server中实现同前缀多表查询并迁移逻辑至存储过程

Alright, let's tackle this problem step by step. You're looking to migrate that C# table-hashing logic into a SQL Server stored procedure, with the main challenge being querying those wildcard-prefixed tables and generating a hash from all matching columns for a given GUID. Since you can't modify table names and have guaranteed unique column names across tables, we can safely build a dynamic solution that works with your existing schema.

Step 1: Break Down the Core Requirements #

We need to:

• Find all tables linked to the target GUID (either as the main table or inherited/attribute tables)
• Join those tables to fetch all columns for the matching row
• Concatenate column names and values into a single, ordered string
• Generate a hash from that string to match your original C# logic

Step 2: Complete Stored Procedure Implementation #

Here's a fully functional stored procedure with comments explaining each critical part:

CREATE PROCEDURE GenerateInheritedObjectHash
    @TargetGUID UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. Collect all tables with a GUID-based key column containing our target GUID
    DECLARE @TablesAndKeys TABLE (
        TableName NVARCHAR(128),
        KeyColumn NVARCHAR(128)
    );

    -- Use a cursor to check each table with a "_key" GUID column for matching rows
    DECLARE @TableName NVARCHAR(128), @KeyColumn NVARCHAR(128);
    DECLARE TableCursor CURSOR FOR
    SELECT 
        t.name, c.name
    FROM 
        sys.tables t
    JOIN 
        sys.columns c ON t.object_id = c.object_id
    WHERE 
        c.system_type_id = TYPE_ID('uniqueidentifier')
        AND c.name LIKE '%[_]key'; -- Matches your key column pattern (e.g., prd_key, pub_prd_key)

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName, @KeyColumn;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Dynamic SQL to verify if the table has a row with our target GUID
        DECLARE @CheckExistsSQL NVARCHAR(MAX) = N'
            IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@KeyColumn) + ' = @TargetGUID)
            BEGIN
                INSERT INTO @TablesAndKeys (TableName, KeyColumn)
                VALUES (@TableName, @KeyColumn)
            END
        ';
        EXEC sp_executesql @CheckExistsSQL, 
            N'@TargetGUID UNIQUEIDENTIFIER, @TableName NVARCHAR(128), @KeyColumn NVARCHAR(128)',
            @TargetGUID = @TargetGUID, @TableName = @TableName, @KeyColumn = @KeyColumn;

        FETCH NEXT FROM TableCursor INTO @TableName, @KeyColumn;
    END

    CLOSE TableCursor;
    DEALLOCATE TableCursor;

    -- 2. Build dynamic SQL to join all matching tables and select all columns
    DECLARE @JoinSQL NVARCHAR(MAX) = N'';
    DECLARE @SelectColumns NVARCHAR(MAX) = N'';
    DECLARE @MainTableName NVARCHAR(128), @MainKeyColumn NVARCHAR(128);

    -- Grab the first table as our base/main table
    SELECT TOP 1 @MainTableName = TableName, @MainKeyColumn = KeyColumn FROM @TablesAndKeys;

    IF @MainTableName IS NOT NULL
    BEGIN
        -- Build SELECT clause (safe since no duplicate column names)
        SELECT @SelectColumns += QUOTENAME(TableName) + '.*, '
        FROM @TablesAndKeys;
        SET @SelectColumns = LEFT(@SelectColumns, LEN(@SelectColumns) - 1); -- Trim trailing comma

        -- Start building the FROM/JOIN statement
        SET @JoinSQL = N'SELECT ' + @SelectColumns + ' FROM ' + QUOTENAME(@MainTableName) + ' main';

        -- Add LEFT JOINs for all other tables (preserves data even if some attribute tables have no match)
        DECLARE JoinCursor CURSOR FOR
        SELECT TableName, KeyColumn FROM @TablesAndKeys WHERE TableName != @MainTableName;

        OPEN JoinCursor;
        FETCH NEXT FROM JoinCursor INTO @TableName, @KeyColumn;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @JoinSQL += N' LEFT JOIN ' + QUOTENAME(@TableName) + ' t ON main.' + QUOTENAME(@MainKeyColumn) + ' = t.' + QUOTENAME(@KeyColumn);
            FETCH NEXT FROM JoinCursor INTO @TableName, @KeyColumn;
        END

        CLOSE JoinCursor;
        DEALLOCATE JoinCursor;

        -- Add the GUID filter
        SET @JoinSQL += N' WHERE main.' + QUOTENAME(@MainKeyColumn) + ' = @TargetGUID';

        -- 3. Concatenate column names and values, then generate hash
        DECLARE @CombinedData NVARCHAR(MAX);
        DECLARE @HashSQL NVARCHAR(MAX) = N'
            -- Combine all column names and values into a single string
            SELECT @CombinedData = STRING_AGG(QUOTENAME(c.name) + ''='' + ISNULL(CONVERT(NVARCHAR(MAX), t.' + QUOTENAME(c.name) + '), ''''), '', '')
            FROM (' + @JoinSQL + ') t
            CROSS APPLY sys.dm_exec_describe_first_result_set(N''' + REPLACE(@JoinSQL, '''', '''''') + ''', NULL, 0) c
        ';

        EXEC sp_executesql @HashSQL,
            N'@TargetGUID UNIQUEIDENTIFIER, @CombinedData NVARCHAR(MAX) OUTPUT',
            @TargetGUID = @TargetGUID, @CombinedData = @CombinedData OUTPUT;

        -- Generate and return the hash (use SHA2_256 for security; swap to MD5 if needed for compatibility)
        SELECT
            HASHBYTES('SHA2_256', @CombinedData) AS ObjectHash,
            @CombinedData AS RawCombinedData -- Optional: return raw string for debugging
    END
    ELSE
    BEGIN
        -- No tables matched the provided GUID
        SELECT
            NULL AS ObjectHash,
            'No tables found with a matching GUID' AS RawCombinedData
    END
END

Step 3: Key Adjustments & Notes #

• Filter by Table Prefix: If you only want to include tables with a specific prefix (e.g., product_), add AND (t.name LIKE 'product_%' OR t.name = 'product') to the initial table selection query feeding the TableCursor.
• Legacy SQL Server Support: STRING_AGG works in SQL Server 2017+. For older versions, replace the concatenation part with this legacy method:

  SELECT @CombinedData = STUFF((
      SELECT ', ' + QUOTENAME(c.name) + '=' + ISNULL(CONVERT(NVARCHAR(MAX), t.' + QUOTENAME(c.name) + '), '')
      FROM sys.dm_exec_describe_first_result_set(N''' + REPLACE(@JoinSQL, '''', '''''') + ''', NULL, 0) c
      FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
  

• Performance Boost: Add non-clustered indexes to all your _key columns—this will drastically speed up the initial check for matching rows in each table.
• Hash Algorithm: The example uses SHA2_256 for security. If you need to match the hash from your original C# code, use the same algorithm (e.g., MD5 if that's what you were using).

Step 4: How to Use It #

Execute the procedure with your target GUID like this:

EXEC GenerateInheritedObjectHash @TargetGUID = 'your-guid-here-1234-5678-90ab-cdef12345678';

This replaces your C# loop with a single database call, which will be significantly faster—especially with large datasets—since it eliminates round-trips between your app and the database.

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