如何从多数据库指定表提取列信息构建MasterDB字典表？

Let's break this down into actionable steps to create and populate your tblCustomer_Dictionary table dynamically—even as you add more databases later on. I'll assume you're working with SQL Server (adjustments can be made for other RDBMS if needed).

Step 1: Create the Target Table (if it doesn't exist) #

First, let's set up the tblCustomer_Dictionary table in your MasterDB to store the column mappings:

USE MasterDB;
GO

CREATE TABLE tblCustomer_Dictionary (
    ColumnName VARCHAR(100) NOT NULL,
    DataBaseName VARCHAR(100) NOT NULL,
    DataBaseID INT NOT NULL,
    db_ColumnName VARCHAR(100) NOT NULL,
    PRIMARY KEY (DataBaseID, db_ColumnName) -- Unique constraint for each DB-column pair
);
GO

Step 2: Dynamic SQL to Populate the Mapping Table #

Since you need to handle an arbitrary number of databases (from tbl_B), we'll use dynamic SQL to loop through each database, fetch its tbl_C columns, map them to the generic names, and insert the records.

Here's the full script:

USE MasterDB;
GO

DECLARE @DBID INT, @DBName VARCHAR(100);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- Cursor to iterate through each database in tbl_B
DECLARE db_cursor CURSOR FOR
SELECT ID, DB_Name FROM tbl_B;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBID, @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build dynamic SQL to get columns from the current DB's tbl_C and map them
    SET @DynamicSQL = N'
    INSERT INTO MasterDB.dbo.tblCustomer_Dictionary (ColumnName, DataBaseName, DataBaseID, db_ColumnName)
    SELECT
        CASE
            WHEN c.name LIKE ''custID%'' THEN ''CustomerID''
            WHEN c.name LIKE ''custName%'' THEN ''CustomerName''
            WHEN c.name LIKE ''CustPhone%'' THEN ''CustomerPhone''
            ELSE ''Unknown'' -- Handle unexpected column names if needed
        END AS ColumnName,
        ''' + @DBName + ''' AS DataBaseName,
        ' + CAST(@DBID AS VARCHAR(10)) + ' AS DataBaseID,
        c.name AS db_ColumnName
    FROM [' + @DBName + '].sys.columns c
    JOIN [' + @DBName + '].sys.tables t ON c.object_id = t.object_id
    WHERE t.name = ''tbl_C'';';

    -- Execute the dynamic SQL
    EXEC sp_executesql @DynamicSQL;

    FETCH NEXT FROM db_cursor INTO @DBID, @DBName;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;
GO

How This Works: #

• Cursor Loop: We use a cursor to go through every entry in tbl_B, pulling the database ID and name each time.
• Dynamic Column Mapping: For each database's tbl_C, we query the system catalog (sys.columns and sys.tables) to get the column names. The CASE statement maps each column to its generic counterpart (e.g., custIDDelhi → CustomerID).
• Insert to Central Table: The mapped data is inserted directly into tblCustomer_Dictionary, linking the generic name to the database-specific column name, database ID, and database name.

Notes for Scalability & Maintenance: #

• Adding New Databases: Just add a new row to tbl_B with the new database's ID and name, then re-run the script to populate the mapping table.
• Handling Column Changes: If tbl_C columns are updated in any database, re-run the script to refresh the mapping (you might want to truncate the table first if you want a full refresh, or add logic to update existing records).
• Permissions: Ensure the account running this script has VIEW DEFINITION access on all target databases (DelhiDB, MumbaiDB, etc.) to read their system catalogs.

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