如何跨多个数据库查询指定表的全部数据并返回关联数据库名称

Got it, let's tackle this problem. You want to pull all data from a specific table across multiple databases, plus keep track of which database each row came from. Here are two solid approaches depending on how many databases you're working with:

Approach 1: Dynamic SQL (Great for lots of databases) #

If you have a bunch of databases to query, manually writing each one out is a pain. Dynamic SQL will automatically build and run the query across all relevant databases for you.

Here's a tailored example based on your single-database query:

DECLARE @TargetSchema NVARCHAR(128) = 'Act';
DECLARE @TargetTable NVARCHAR(128) = 'User';
DECLARE @WhereClause NVARCHAR(MAX) = 'WHERE ukey = 2'; -- Adjust your filter here
DECLARE @OrderByClause NVARCHAR(MAX) = 'ORDER BY createdate'; -- Keep your sorting

DECLARE @DynamicSQL NVARCHAR(MAX) = '';

-- Build the query string for each non-system database
SELECT @DynamicSQL += 
    'SELECT ''' + name + ''' AS SourceDatabase, * FROM [' + name + '].[' + @TargetSchema + '].[' + @TargetTable + '] ' + @WhereClause + ' UNION ALL '
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- Skip system databases
AND state_desc = 'ONLINE'; -- Only include databases that are online

-- Remove the trailing "UNION ALL" that gets added to the end
SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10);

-- Add the ORDER BY clause if you need it
IF @OrderByClause IS NOT NULL AND @OrderByClause <> ''
BEGIN
    SET @DynamicSQL += ' ' + @OrderByClause;
END

-- Execute the assembled query
EXEC sp_executesql @DynamicSQL;

How this works: #

• We define the schema, table, filter, and sort order upfront.
• We query sys.databases to get all user databases (excluding system ones).
• For each database, we add a query that includes the database name as a SourceDatabase column (so you know where each row came from).
• We combine all those queries with UNION ALL and run the whole thing.

Approach 2: Manual UNION ALL (Perfect for a small set of databases) #

If you only have a few databases to check, writing the query manually is simpler and less prone to unexpected issues.

Here's how to adapt your original query:

SELECT 'Database1' AS SourceDatabase, * FROM Database1.Act.User WHERE ukey = 2
UNION ALL
SELECT 'Database2' AS SourceDatabase, * FROM Database2.Act.User WHERE ukey = 2
UNION ALL
SELECT 'Database3' AS SourceDatabase, * FROM Database3.Act.User WHERE ukey = 2
ORDER BY createdate;

Just repeat the SELECT block for each database you need to include, and make sure to update the SourceDatabase value to match the actual database name.

Quick Notes to Keep in Mind: #

• Table Structure Consistency: All the target tables across databases need to have matching column structures (same columns, data types) if you use *. If there are differences, list out specific columns instead of using * to avoid errors.
• Permissions: Make sure your user account has read access to every database and table you're querying.
• SQL Injection: If you're using dynamic SQL and pulling database/schema/table names from user input, sanitize those inputs to prevent injection risks. The dynamic SQL example above uses hardcoded variables, so it's safe here.

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