juur juur - 7 months ago 8
SQL Question

SQL Server: should I use information_schema tables over sys tables?

In SQL Server there is two schemas for metadata:


  • INFORMATION_SCHEMA

  • SYS



I have heard that
INFORMATION_SCHEMA
tables are based on ANSI standard. When developing e.g. stored procedures, should it be wise to use
INFORMATION_SCHEMA
tables over
sys
tables?

Answer

I would always try to use the Information_schema views over querying the sys schema directly.

The Views are ISO compliant so in theory you should be able to easily migrate any queries across different RDBMS.

However, there have been some cases where the information that I need is just not available in a view.

I've provided some links with further information on the views and querying a SQL Server Catalog.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

http://msdn.microsoft.com/en-us/library/ms189082.aspx

Comments