SQL - INFORMATION_SCHEMA for All Databases On Server

sql sql-server information-schema
Ahmet Altun picture Ahmet Altun · Apr 26, 2011 · Viewed 12.2k times · Source

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

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

Answer

Dalex picture Dalex · Apr 26, 2011

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.

Related questions

How do I find a default constraint using INFORMATION_SCHEMA?

I'm trying to test if a given default constraint exists. I don't want to use the sysobjects table, but the more standard INFORMATION_SCHEMA. I've used this to check for tables and primary key constraints before, but I don't see …

Read More
SQL Server: Howto get foreign key reference from information_schema?

In SQL Server, how can I get the referenced table + column name from a foreign key? Note: Not the table/column where the key is in, but the key it refers to. Example: When the key [FA_MDT_ID] in …

Read More
Find all tables whose name ends with a certain suffix

I have thousand of tables in database. Some names end with _History. For example : abc_History bcd_History 123_History How do I find all tables which name is end with _History. Some thing like: SELECT table_name FROM sys.tables …

Read More