多值全表检索：获取含目标值的唯一表/列信息需求

Got it, let's tackle this. You already have a solid stored procedure that pulls table names, column names, and their values—now you want to tweak it to only return unique (table, column) pairs where your target values exist, using JOINs for better performance. Perfect call on the JOIN approach; it's often more efficient than subqueries for this kind of filtered, deduplicated result set.

First, Let's Align on the Core Logic #

Your end goal is to get a clean list of tables and columns that contain any of your target values, so you can build dynamic UPDATEs later. Here's how to adapt your existing SP with JOINs:

1. Assume Your Existing SP's Output Structure

Let's say your current SP outputs a result set like this (or uses a temp table/table variable with these columns):

CREATE TABLE #ExistingResults (
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    ColumnValue SQL_VARIANT
);

(If your SP doesn't use a temp table, modify it to populate one first—this makes the JOIN step straightforward.)

2. Create a Target Values List

First, define the values you're searching for. Using a table-valued parameter (TVP) is the cleanest, type-safe way to pass these in:

-- Create a reusable TVP type for target values
CREATE TYPE TargetValues AS TABLE (TargetValue SQL_VARIANT);
GO

For testing, you can also use a temp table:

CREATE TABLE #TargetValues (TargetValue SQL_VARIANT);
INSERT INTO #TargetValues VALUES ('OldCustomerName'), ('LegacyStatus'); -- Add your target values here

3. Use JOIN to Filter and Deduplicate

Instead of returning all rows from your existing result set, JOIN it with your target values list, then deduplicate to get unique table/column pairs:

-- This gives you the exact unique pairs you need
SELECT DISTINCT er.TableName, er.ColumnName
FROM #ExistingResults er
JOIN #TargetValues tv ON er.ColumnValue = tv.TargetValue;

If you prefer GROUP BY over DISTINCT for clarity, you can rewrite it as:

SELECT er.TableName, er.ColumnName
FROM #ExistingResults er
JOIN #TargetValues tv ON er.ColumnValue = tv.TargetValue
GROUP BY er.TableName, er.ColumnName;

Optimizing the JOIN Approach #

• Indexing: If your #ExistingResults temp table is large, add an index on ColumnValue to speed up the JOIN:

  CREATE NONCLUSTERED INDEX IX_ExistingResults_ColumnValue ON #ExistingResults (ColumnValue);
  

• Filter Early: Modify your original SP to only check columns that could contain your target values (e.g., skip INT or DATE columns if your targets are strings) by filtering INFORMATION_SCHEMA.COLUMNS first. This reduces the data you need to process.
• TVP Benefits: Using a TVP instead of comma-separated strings makes your SP more flexible and avoids messy string parsing.

Full Refactored SP Example #

Here's how the complete SP might look, using a TVP for target values:

CREATE PROCEDURE FindTablesColumnsWithTargetValues
    @TargetValues TargetValues READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- Step 1: Populate existing results (adapt this to your original SP's logic)
    CREATE TABLE #ExistingResults (
        TableName NVARCHAR(128),
        ColumnName NVARCHAR(128),
        ColumnValue SQL_VARIANT
    );

    -- Replace this loop with your original SP's logic to fill #ExistingResults
    DECLARE @TableName NVARCHAR(128), @ColumnName NVARCHAR(128), @SQL NVARCHAR(MAX);
    DECLARE cur CURSOR FOR
        SELECT TABLE_NAME, COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar'); -- Filter relevant data types

    OPEN cur;
    FETCH NEXT FROM cur INTO @TableName, @ColumnName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'INSERT INTO #ExistingResults SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', ' + QUOTENAME(@ColumnName) + ' FROM ' + QUOTENAME(@TableName);
        EXEC sp_executesql @SQL;

        FETCH NEXT FROM cur INTO @TableName, @ColumnName;
    END

    CLOSE cur;
    DEALLOCATE cur;

    -- Step 2: JOIN with target values and return unique pairs
    SELECT DISTINCT er.TableName, er.ColumnName
    FROM #ExistingResults er
    JOIN @TargetValues tv ON er.ColumnValue = tv.TargetValue;

    DROP TABLE #ExistingResults;
END
GO

Why This Works Better Than Other Approaches #

Using a JOIN instead of subqueries (like WHERE ColumnValue IN (...)) lets the query optimizer use more efficient execution plans, especially with large datasets. Deduplicating after the JOIN ensures you only get each (table, column) pair once—exactly what you need to build your dynamic UPDATE statements later.

Just adapt the #ExistingResults population step to match your original SP's logic, and you're ready to go!

内容的提问来源于stack exchange，提问作者Chester van Ree