Most of my database experience is with Oracle; however, I’m currently helping a client that uses SQL Server 2005. I’m used to obtaining metadata by querying ALL_TABLES and ALL_TAB_COLUMNS (or USER_TABLES/USER_TAB_COLUMNS or DBA_TABLES/DBA_TAB_COLUMNS). How can similar information be obtained from SQL Server using similar techniques to ALL_TABLES/ALL_TAB_COLUMNS in Oracle?
Metadata can be queried in SQL Server using the INFORMATION_SCHEMA. For example, the following query identifies columns in one table that do not exist in another (actually, a view based on the table).
WHERE source.TABLE_CATALOG =‘FACT’
AND source.TABLE_SCHEMA =‘dbo’
AND source.TABLE_NAME =‘FCT_FREIGHT’
WHERE missing.TABLE_CATALOG =‘FACT’
AND missing.TABLE_SCHEMA =‘dbo’
AND missing.TABLE_NAME =‘FCT_FREGHT_EV’
AND missing.COLUMN_NAME = source.COLUMN_NAME
This is just one application of using database metadata. Visit MSDN, the Microsoft Developer’s Network, for more information about the INFORMATION_SCHEMA.