Microsoft SQL Server and INFORMATION_SCHEMA

Looking for Microsoft SQL Server metadata?

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?

 

Microsoft SQL Server Information SchemaMetadata 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.

Welcome to my blog!

My first blog post, written on October 29, 2007.

OK, here goes. My first blog post. I began working with Business Objects 5.x in 2003. It’s hard to believe that in almost five years, I’ve seen four major releases, grown from novice to certified trainer, and presented breakouts at two Business Objects user conferences. My goal with this blog is to periodically post tips and tricks for working with various aspects of Business Objects Enterprise.