当前位置：文江博客话题详情

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

发布于 2024-11-03 08:38:00 字数 163 浏览 0 评论 0原文

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

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

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海螺姑娘 2024-11-10 08:38:01

修改了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

Modified Dustin's code (from Dalex's suggestion) to accommodate database names with spaces and eliminate common system tables from results.

--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

回复收藏 0 原文

偏闹i 2024-11-10 08:38:01

你可以使用这个：

SELECT TABLE_SCHEMA
FROM information_schema.tables
group by tables.TABLE_SCHEMA

You can use this:

SELECT TABLE_SCHEMA
FROM information_schema.tables
group by tables.TABLE_SCHEMA

回复收藏 0 原文

作死小能手 2024-11-10 08:38:01

这不是问题的答案，但此文本添加了上下文......并且文本可能对某人获得理解有用。

可能并且经常需要添加一个 use 子句来选择在 select 子句上方引用哪个数据库..

例如

use CaseData

SELECT *
FROM information_schema.columns 

--WHERE 

--TABLE_CATALOG = 'CaseData'

--and TABLE_SCHEMA ='Clinical'

--and 

--TABLE_NAME = 'SAASCaseData_NewFieldsOct2018'

This isn't the answer to the question but this text adds context ... and text is likely to be useful to someone to gain understanding.

It is possible and often required to add a use clause to select which database is being referenced above the select clause ..

e.g.

use CaseData

SELECT *
FROM information_schema.columns 

--WHERE 

--TABLE_CATALOG = 'CaseData'

--and TABLE_SCHEMA ='Clinical'

--and 

--TABLE_NAME = 'SAASCaseData_NewFieldsOct2018'

回复收藏 0 原文

心奴独伤 2024-11-10 08:38:01

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

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

回复收藏 0 原文

贵在坚持 2024-11-10 08:38:00

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

回复收藏 0 原文

他是夢罘是命 2024-11-10 08:38:00

将 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

Expanding Dalex's answer into code.

--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

回复收藏 0 原文

~没有更多了~