比较多个SQL Server数据库中的表名

Question

我正在尝试运行SQL Server导出，该导出比较SQL Server实例上三个或多个数据库中列出的表名(实际上这个数字要高得多！(

我可以使用以下简单的方法来获取特定数据库中所有表的列表。

SELECT [name] 
FROM DB1.sys.tables 

我想做的是能够将这些数据库连接到多个数据库中，以创建一个视图来发现差异(和相似性(。

例如，如果我有三个数据库，其中有以下表

DB1:

Customers
Orders
Products

DB2:

Products
Orders

DB3:

Products
Suppliers

我怎么能把它们放在一起得到下面这样的东西呢？

空

DB1	DB2	DB3
客户	NULL	NULL
订单		订单
产品		产品
NULL	NULL	供应商

这个脚本应该能提供您想要的内容，并且可以通过在第20行左右篡改WHERE子句来轻松扩展

--Set up some global temporary tables
CREATE TABLE ##all_tables(
[database_name] sysname,
[table_name] sysname
)
CREATE TABLE ##tables_for_relevant_dbs(
[database_name] sysname,
[table_name] sysname
)
--Capture a list of all the tables in all of the databases on the instance
exec sp_MSforeachdb 'insert into ##all_tables select ''?'', [name] from [?].sys.tables'
--Filter to get the tables from the relevant databases
INSERT INTO ##tables_for_relevant_dbs 
SELECT dbs.[database_name],tbls.[table_name]
FROM (select distinct database_name from ##all_tables) dbs
LEFT JOIN ##all_tables tbls on tbls.database_name=dbs.database_name
WHERE dbs.database_name like '%' --PUT SOMETHING ELSE HERE
--This magic TSQL concatenates a column of values into a single string
--This string is then used in the dynamic pivoting below
DECLARE @dblist VARCHAR(MAX)
SELECT 
@dblist = STUFF((SELECT ', [' + d_name + ']' AS [text()]
FROM (SELECT DISTINCT 
database_name d_name 
FROM ##tables_for_relevant_dbs) d 
ORDER BY d_name
FOR XML PATH('')), 1, 1, '')
--Pivot the data to turn the database names into columns
EXEC ('SELECT ' + @dblist + '
FROM (SELECT at1.database_name, at2.table_name src, at1.table_name dest
FROM ##tables_for_relevant_dbs at1
LEFT JOIN ##tables_for_relevant_dbs at2 on at2.database_name=at1.database_name and at2.table_name=at1.table_name) t
PIVOT (max(t.src) FOR [database_name] IN (' + @dblist + ')) as [PVT]')
--Clean up
DROP TABLE ##all_tables
DROP TABLE ##tables_for_relevant_dbs

使用公共表表达式获取信息模式表中的表名，并使用完全外部联接即可。

;with tables_in_DB1 as
(
select * from DB1.INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
), 
tables_in_DB2 as
(
select * from DB2.INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
), 
tables_in_DB3 as
(
select * from DB3.INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='BASE TABLE'
) 
select db1.table_name as DB2,db2.table_name as DB2,
db3.table_name as DB3 from tables_in_DB1 db1 
full outer join tables_in_DB2 db2 on db1.TABLE_NAME=db2.TABLE_NAME 
full outer join tables_in_DB3 db3 on db2.TABLE_NAME=db3.TABLE_NAME