将数据库名称与表列表关联

Question

将数据库名称与表列表关联

sqldatabasesql-server-2008

5

在服务器上列出所有带有模式的表不是问题。

SELECT SCHEMA_NAME(schema_id), name FROM sys.tables

我该如何确定表所在的数据库？

- DarkRiver

目标不仅仅是返回当前数据库中表的列表，这点已经得到了完美的回答。我需要比较两个物理服务器以确定它们在 database.schema.table 和 columns 方面是否相同。我试图定义一个方法，该方法将提供完整的数据库.schema.table 列表，可能比使用光标迭代每个数据库来收集表更好。 - DarkRiver

1

针对这个需求，不要重复造轮子。有很多非常好的、免费的、介于两者之间的解决方案可以解决这个问题，而且不需要你写任何代码。点击一下，完成。http://bertrandaaron.wordpress.com/2012/04/20/re-blog-the-cost-of-reinventing-the-wheel/ - Aaron Bertrand

3个回答

4

我在创建查询时遇到了这个问题。我希望能够运行该查询并包含另一个数据库的名称，而不是将其硬编码到查询中。

查询大致如下：

SELECT DB_NAME() db_name
     , SCHEMA_NAME(schema_id) schema_name
     , name table_name
  FROM OtherDB.sys.tables   --The OtherDB is to specify that I am running
                            --this for a different database than the one
                            --I'm logged in to for my current session.

问题在于，尽管我在from子句中指定了OtherDB.sys.tables，但DB_NAME()总是返回我所在的当前数据库。是的，我可以在开头加上USE OtherDB，但似乎应该有另一种方法。我查看了我能找到的每个sys视图，但从来没有找到任何将sys.databases和sys.tables链接起来的东西。

我最终发现的是SQL Server的INFORMATION_SCHEMA.TABLES。这个视图将数据库名称作为第一列（称为TABLE_CATAOLG）。

SELECT TABLE_CATALOG
     , TABLE_SCHEMA
     , TABLE_NAME
     , TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES

使用这些视图，您可以轻松比较两个数据库中的表格：

SELECT a.TABLE_CATALOG
     , a.TABLE_SCHEMA
     , a.TABLE_NAME
     , a.TABLE_TYPE
     , b.TABLE_CATALOG
     , b.TABLE_SCHEMA
     , b.TABLE_NAME
     , b.TABLE_TYPE
  FROM OneDatabase.INFORMATION_SCHEMA.TABLES a
  FULL OUTER JOIN TwoDatabase.INFORMATION_SCHEMA.TABLES b 
    ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
   AND a.TABLE_NAME = b.TABLE_NAME

如果数据库在链接的不同服务器上，您应该能够通过使用完全限定表名的所有四个部分来使用此查询。

- Andrew0002

1

如果您的意图只是包含当前数据库名称，为什么不直接这样做：

SELECT DB_NAME(), SCHEMA_NAME(schema_id), name FROM sys.tables;

如果您的意图是从所有数据库中提取所有名称，我个人更喜欢像这样的动态SQL，而不是使用sp_msforeachdb：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + 'UNION ALL 
    SELECT ''' + name + ''', s.name, t.name
    FROM ' + QUOTENAME(name) + '.sys.tables AS t
    INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
    ON t.schema_id = s.schema_id'
    FROM sys.databases
    WHERE database_id > 4;

SET @sql = STUFF(@sql, 1, 13, '');

PRINT @sql;

-- EXEC sp_executesql @sql;

- Aaron Bertrand

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- SQLMenace · Accepted Answer

sys.tables存在于所有数据库中，所以我不明白你不知道自己在哪个数据库中的事实。你可以运行DB_NAME（DB_ID（））来获取数据库名称。

SELECT  DB_NAME(DB_ID()),SCHEMA_NAME(schema_id), name FROM sys.tables

但是在这种情况下，DB_NAME（DB_ID（））将为每一行返回相同的值。

要对所有数据库执行此操作，可以这样做

EXEC sp_msforeachdb 'use  [?] SELECT  ''?'',SCHEMA_NAME(schema_id), name 
                                  FROM sys.tables'

当然，您也可以将它倒入表格中

CREATE TABLE #output (DatabaseName VARCHAR(1000), 
                   SchemaName VARCHAR(1000), 
                  TableName VARCHAR(1000))

INSERT #output
EXEC sp_msforeachdb 'use  [?] SELECT  ''?'',SCHEMA_NAME(schema_id), name 
                                  FROM sys.tables'

SELECT * FROM #output

仅作为提醒，sp_msforeachdb 存储过程是未记录的，您不应该将其用于生产代码。如果只是为了快速查找，那么可以使用它，但对于生产代码，请编写自己的版本。

另请参阅 Aaron Bertrand 的这些文章：