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
FROM INFORMATION_SCHEMA.COLUMNS source
WHERE source.TABLE_CATALOG = ‘FACT’
AND source.TABLE_SCHEMA = ‘dbo’
AND source.TABLE_NAME = ‘FCT_FREIGHT’
AND NOT EXISTS
(
SELECT ‘X’
FROM INFORMATION_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
)
ORDER BY source.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS source
WHERE source.TABLE_CATALOG = ‘FACT’
AND source.TABLE_SCHEMA = ‘dbo’
AND source.TABLE_NAME = ‘FCT_FREIGHT’
AND NOT EXISTS
(
SELECT ‘X’
FROM INFORMATION_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
)
ORDER BY 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.
