Microsoft SQL Server and INFORMATION_SCHEMA

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
NOTEXISTS
(
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
)
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.

Author: Dallas Marks

I am a business intelligence architect, author, and trainer. I help organizations harness the power of analytics, primarily with SAP BusinessObjects products. An active blogger, SAP Mentor and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting about business intelligence.

Leave a Reply