在服务器上获取所有数据库的SQL - INFORMATION_SCHEMA

Question

INFORMATION_SCHEMA.TABLES或INFORMATION_SCHEMA.COLUMNS仅适用于指定的数据库。

是否可以通过使用INFORMATION_SCHEMA查询服务器上所有数据库的表元数据？

- Ahmet Altun

---

你的用户是否具有足够的访问权限来执行查询？ - Adnan

假设我有了，解决方案会是什么？ - Ahmet Altun

Answer 1

你只能通过使用动态查询来进行数据库迭代。一种方法是使用ms_ForEachDB存储过程，第二种方法是查询sys.databases动态视图。

- Dalex

Answer 2

将Dalex的答案转化为代码。

--Make sure you have a global temporary table to use. Double dots are shorthand for .dbo.
IF OBJECT_ID('tempdb..##test') IS NOT NULL DROP TABLE ##test
--Create the table definition the easy way.
SELECT * INTO ##test
FROM ???.INFORMATION_SCHEMA.TABLES --The ??? will be whatever the name of your first database is.
DELETE FROM ##test
--Add all the data.
EXEC sp_MSforeachdb 'USE ? INSERT INTO ##test SELECT * FROM INFORMATION_SCHEMA.TABLES'
--View all the data.
SELECT * FROM ##test
--Clean up.
DROP TABLE ##test

- Dustin Morrison

Answer 3

修改了Dustin的代码（来自Dalex的建议），以适应具有空格的数据库名称并从结果中删除常见的系统表。

--Make sure you have a global temporary table to use. Double dots are shorthand for .dbo.
IF OBJECT_ID('tempdb..##test') IS NOT NULL DROP TABLE ##test

--Create the table definition the easy way.
SELECT top 1 * INTO ##test
FROM INFORMATION_SCHEMA.TABLES
DELETE FROM ##test

--Add all the data.
EXEC sp_MSforeachdb 'USE [?] INSERT INTO ##test SELECT * FROM INFORMATION_SCHEMA.TABLES'

--View all the data.
SELECT * FROM ##test
WHERE TABLE_CATALOG NOT IN ('master','tempdb', 'msdb')
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

--Clean up.
DROP TABLE ##test

- Baron

---

这些常见的系统表是否与特定供应商有关？如果是，哪个属于哪个供应商？ - volante

Answer 4

你可以使用这个：

SELECT TABLE_SCHEMA
FROM information_schema.tables
group by tables.TABLE_SCHEMA

- Marino D Ignazio

---

尽管这段代码可能回答了问题，但提供关于它为什么以及如何回答问题的额外上下文会显著提高其长期价值。请编辑您的答案以添加一些解释。特别是，这是否一次性获取所有数据库的元数据？根据我所知（基于MyQSL和PostgreSQL），它只会返回当前数据库的表元数据。在这方面，SQL Server是否不同？ - Toby Speight

这个回答没有解答问题。提供的查询仅返回当前数据库上下文的数据。如果您的服务器有多个数据库（TABLE_CATALOG），则它不会显示所有数据库，这是该问题所要求的。请尝试这个查询 SELECT DISTINCT TABLE_CATALOG FROM information_schema.tables，您将只看到一个值。 - Davos

Answer 5

这不是问题的答案，但这段文字增加了背景信息...对于某些人理解内容很有帮助。

在选择子句之前添加use语句来选择引用哪个数据库通常是可能的和必要的...

例如：

use CaseData

SELECT *
FROM information_schema.columns 

--WHERE 

--TABLE_CATALOG = 'CaseData'

--and TABLE_SCHEMA ='Clinical'

--and 

--TABLE_NAME = 'SAASCaseData_NewFieldsOct2018'

- Allan F

Answer 6

SELECT DISTINCT `TABLE_SCHEMA` FROM `information_schema`.`TABLES`;

- ikbal hossain

---

该问题本身表明这不起作用。在 SQL Server 中，上述查询仅返回运行查询的数据库的架构，并且不跨数据库运行。 - Sumit Maingi