A Primer on INFORMATION_SCHEMA Views in SQL Server 2000 - Internals of INFORMATION_SCHEMA views
(Page 4 of 5 )
Now that you have enough of an understanding of INFORMATION_SCHEMA views, let us move on to further internals of INFORMATION_SCHEMA views.
What made the INFORMATION_SCHEMA views so powerful? Are they re-written by Microsoft to help the application programmers? Do they having any relation to system stored procedures or functions? Can we create our own views together with INFORMATION_SCHEMA views and some system stored procedures?
Several questions could be extracted from our present knowledge on INFORMATION_SCHEMA. But, primarily there is a fundamental answer for any of the questions above.
INFORMATION_SCHEMA VIEWS ARE JUST THE VIEWS BASED ON EXISTING SYSTEM STORED PROCEDURES OR FUNCTIONS.
I hope the above answers almost all of the questions. Microsoft made our life easier by creating the INFORMATION_SCHEMA views on top of system stored procedures. Now, how do you know the internal query of a certain view present in INFORMATION_SCHEMA? This is a very simple and situational question according to the current topic. Of course, the answer is also simple, too. Let us consider the following.
I would like to know how Microsoft SQL Server team implemented INFORMATION_SCHEMA.TABLES view. Just type the following in your query analyzer (generally using ‘master’ database), and you should be able to see the result.
execute sp_helptext "information_schema.tables"
The result returned by that would be as following:
create view INFORMATION_SCHEMA.TABLES
as
select distinct
db_name() as TABLE_CATALOG
,user_name(o.uid)as TABLE_SCHEMA
,o.name as TABLE_NAME
,case o.xtype
when 'U' then 'BASE TABLE'
when 'V' then 'VIEW'
end as TABLE_TYPE
from
sysobjects o
where
o.xtype in ('U', 'V') and
permissions(o.id) != 0
I hope you understand the secret behind the INFORMATION_SCHEMA views now. They are nothing but the queries written by the SQL Server team to make our life easier, instead forcing us to remember a complicated query using the system stored procedures and functions.
Next: Summary >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 35 
