Using catalog views in SQL Server 2005

  • Article

Did you know that the catalog views in SQL Server 2005 exposes metadata for various objects in a database and at the server-level? This is the preferred method of accessing metadata. It is a much richer mechanism that doesn't require access to system tables or undocumented columns or status bits. If you want standard and portable access to metadata then you can still use the INFORMATION_SCHEMA views but it is limited to standard specific features, data types and views.

 

I have posted several topics in the past that shows how to use the information in various catalog views. And the Books Online Documentation has a topic that has answers to several frequently asked questions. It shows you how to use the catalog views to get answers to following questions for example:

 

How do I find the LOB data types of a specified table?
How do I find the CHECK constraints that depend on a specified CLR user-defined type?

How do I find the dependencies on a specified function?

How do I find all the owners of entities contained in a specified schema?

 

You can find the topic using the links below:

 

Books Online in MSDN: https://msdn2.microsoft.com/en-us/library/ms345522.aspx

Books Online Client: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ca202580-c37e-4ccd-9275-77ce79481f64.htm

 

Please check it out and send your feedback using the Books Online feedback mechanism if you want to see additional questions. You can also post such questions here and I will write the queries using the catalog views.

--

Umachandar

Comments

  • Anonymous
    September 07, 2006
    So should we now suggest people use the catalog views, rather than information schema?  I am really liking the new views, and the information schema is clunky at best, but it is pretty much the same in 2005 as it was in 2000.  The system tables of 2000 are all TOTALLY different :)

    Is there a pledge to maintain these interfaces for a good long time to go?

    Louis
  • Anonymous
    September 08, 2006
    Catalog views are useful if your code is primarily targetting SQL Server 2005 or higher and it also contains information that is proprietary & specific to SQL Server. The catalog views schema will follow the standard deprecation policy for SQL Server features. So we will preserve backward compatibility wherever and whenever possible. If we do modify the schema for catalog views then it will go through the deprecation procedure with announcement in version N and removal after say version N + 3. The catalog views should not be confused with the dynamic management views/functions which can change between releases or even in service packs.

    And INFORMATION_SCHEMA views still have their place. For example, if you have some application code that needs to work against different SQL Server versions or vendors then it is best to use the ANSI standard views. Performance of queries in INFORMATION_SCHEMA views was a concern in prior versions of SQL Server. This should no longer be the case since the ANSI standard views have been rewritten to use the new catalog views.

    So the use of catalog views vs INFORMATION_SCHEMA views depends on the scenario and requirements.

    --
    Umachandar