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).
— INFORMATION_SCHEMA query to identify columns missing in database view but present in source table.SELECT source.TABLE_CATALOG, source.TABLE_SCHEMA, source.TABLE_NAME, source.COLUMN_NAME, source.ORDINAL_POSITION, SOURCE.data_type
FROMINFORMATION_SCHEMA.COLUMNS source
WHERE source.TABLE_CATALOG =‘FACT’
AND source.TABLE_SCHEMA =‘dbo’
AND source.TABLE_NAME =‘FCT_FREIGHT’
ANDNOTEXISTS
(
SELECT‘X’
FROMINFORMATION_SCHEMA.COLUMNS missing
WHERE missing.TABLE_CATALOG =‘FACT’
AND missing.TABLE_SCHEMA =‘dbo’
AND missing.TABLE_NAME =‘FCT_FREGHT_EV’
AND missing.COLUMN_NAME = source.COLUMN_NAME
)
ORDERBY source.ORDINAL_POSITION
FROMINFORMATION_SCHEMA.COLUMNS source
WHERE source.TABLE_CATALOG =‘FACT’
AND source.TABLE_SCHEMA =‘dbo’
AND source.TABLE_NAME =‘FCT_FREIGHT’
ANDNOTEXISTS
(
SELECT‘X’
FROMINFORMATION_SCHEMA.COLUMNS missing
WHERE missing.TABLE_CATALOG =‘FACT’
AND missing.TABLE_SCHEMA =‘dbo’
AND missing.TABLE_NAME =‘FCT_FREGHT_EV’
AND missing.COLUMN_NAME = source.COLUMN_NAME
)
ORDERBY source.ORDINAL_POSITION
This is just one application of using database metadata. Visit MSDN, the Microsoft Developer’s Network, for more information about the INFORMATION_SCHEMA.
