Thursday, 21 November 2013

Difference between INFORMATION_SCHEMA and SYS Objects in SQL Server



1.    INFORMATION_SCHEMA is an ANSI standard set of READ-ONLY views which provide information about all of the tables, views, columns, and procedures in a database. All RDBMS have to provide this views and our query would work in all RDBMS (oracle, sql, mysql etc.) On the other hand SYS schema is specific to SQL product and would have much more details.

2.    SYS objects would be having more information related to SQL Objects rather than INFORMATION_SCHEMA.

The INFORMATION_SCHEMA is part of the SQL-92 standard, so we can use it in other database engines in future stage. In SQL Server, these views exist in each database. It is not under the "dbo" user. Instead they are under the ownership of INFORMATION_SCHEMA. Therefore, to access any of the views, we need to preface the view name with "INFORMATION_SCHEMA which is like below.

SELECT * FROM INFORMATION_SCHEMA.TABLES


Each of the views in this collection exposes a different aspect of the meta data of the database.