SQL - INFORMATION_SCHEMA for All Databases On Server

INFORMATION_SCHEMA.TABLES or INFORMATI开发者_Python百科ON_SCHEMA.COLUMNS work for only specified databases.

Is it possible to query table metadata for ALL databases on server by using INFORMATION_SCHEMA?

---

You can do this only by using dynamic query for database iteration. One way is using ms_ForEachDB stored procedure, second is querying sys.databases dynamic view.

---

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

---

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

---

You can use this:

SELECT TABLE_SCHEMA
FROM information_schema.tables
group by tables.TABLE_SCHEMA

---

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'

---

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