Perfect one shot for the difference between INFORMATION_SCHEMA and the SYSTEM VIEWS

 

SQL Studies

I spend a reasonable amount of time looking through various forums, answering some questions and reading the answers to others. One of the things I’ve noticed is that while I frequently see system views referenced I only very rarely see the INFORMATION_SCHEMA views mentioned. This seems rather strange to me since I think most (although by no means all) of the people who work with system views know about the INFORMATION_SCHEMA ones.

For those who haven’t seen them before I’m going to show a few examples, comparing each INFORMATION_SCHEMA (owned by the INFORMATION_SCHEMA schema) view to the more common sys (owned by the sys schema) system view I see. Note: I’m only showing the base sys system view. In most (if not all) cases retrieving the same information will require joining several sys system views and/or functions together.

INFORMATION_SCHEMA.TABLES vs sys.tables
INFORMATION_SCHEMA_TABLES

INFORMATION_SCHEMA.COLUMNS vs sys.columns
INFORMATION_SCHEMA_COLUMNS

INFORMATION_SCHEMA.VIEWS vs sys.views
INFORMATION_SCHEMA_VIEWS

INFORMATION_SCHEMA.VIEW_TABLE_USAGE vs sys.dependencies

View original post 483 more words

Advertisements