SQL - INFORMATION_SCHEMA for all databases on the server

Question

INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.COLUMNS only work for specific databases.

Can I request table metadata for ALL databases on a server using INFORMATION_SCHEMA?

Answer 1

You can only do this with a dynamic query to iterate the database. One way is to use the ms_ForEachDB stored procedure, the second in the sys.databases dynamic view.

Answer 2

You can use this:

SELECT TABLE_SCHEMA FROM information_schema.tables group by tables.TABLE_SCHEMA 

Answer 3

This is not the answer to the question, but this text adds context ... and the text is likely to be useful for understanding.

It is possible and often necessary to add a use clause to select the database referenced above by the select clause.

eg

 use CaseData SELECT * FROM information_schema.columns --WHERE --TABLE_CATALOG = 'CaseData' --and TABLE_SCHEMA ='Clinical' --and --TABLE_NAME = 'SAASCaseData_NewFieldsOct2018' 

Answer 4

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