Regarding INFORMATION_SCHEMA views | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Regarding INFORMATION_SCHEMA views

Hi, In master database, we have views like CHECK_CONSTRAINTS, COLUMN_DOMAIN_USAGE, COLUMN_PRIVILEGES, etc., INFORMATION_SCHEMA is the owner for these objects. Is it possible to view the contents of these views, if so, how do we do it? Thank you. Regards,
Deva


Use master
EXEC sp_helptext ‘INFORMATION_SCHEMA.TABLES’

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
It depends on what exactly you understand to be the "contents" of a view. Use either Frank’s syntax to get the definition of the view, or the following syntax to retrieve the information from the view: SELECT * FROM INFORMATION_SCHEMA.TABLES
Aah, I see. The INFORMATION_SCHEMA views are located only in the master db, but they will work and report on the current db from which you call them. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
or select text from master..syscomments where text like ‘%INFORMATION_SCHEMA.TABLES%’
and colid=1 Also if you dont specify the Dabasename, INFORMATION_SCHEMA.TABLES will point to the database from which it is accessed
Madhivanan Failing to plan is Planning to fail
You don’t have to be connected to the database about which you want to see the view: you can also put the database name in front: SELECT * FROM mydatabase.INFORMATION_SCHEMA.TABLES
Hi, Thank you all for the help, I actually wanted to see the view definition, when I used the following sql statement in master database, sp_helptext Tables It gives the following error, The object ‘Tables’ does not exist in database ‘master’ Now, I used as per your suggested, I could see the view definition, thank you. Regards,
Deva
SP_HELPTEXT lists the content of a stored procedure, trigger or a view and you’re referring an object called TABLES and the error generated is quite right. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Table definitions will be available if you use sp_help tables. It wont generate Create Table script. Madhivanan Failing to plan is Planning to fail
If you simply write
sp_helptext ‘Tables’ it will never report the definition of INFORMATION_SCHEMA.TABLES as this is a completely different owner. You need to explicitely refer to INFORMATION_SCHEMA to get the expected result.
And, btw, when referring to the INFORMATION_SCHEMA views, it’s alwas a good idea to use uppercases as on some collations lowercases won’t work, IIRC. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Hi, Thank you all for the suggestions/explanations, it is helpful for me. Regards,
Deva
]]>