All you wanted to know about the INFORMATION_SCHEMA (MsSQL) and then some :)
Tue, Jul 11 2006 5:59 PM
Information_schema is a part of the information stored in the database and is the preferred gateway to this information.
It's a sql 92 standard, which means that it's supposed to be standard but every database is bound to have it's own implementation of it and will do things differently.
Some of the differences of MSSQL 2000 vs the standard and basic information.
|SQL Server name
|| SQL-92 name|
|user-defined data type
Ok now we know what it is are what can we do with it :)
Well we can look up with the help of some dynamic sql, where for instance one term is in all the tables of one database.
We can use it for one of the things most people do with it.
We can use it to do bulletproof updates of database schema's.
We can use it to tell differences between schema's
Build all in one change tracking solutions.
Build little utility scripts.
Offcourse a lot of stuff is also available through a ADO.Net interface which is through the use of "GetSchema" in .Net 2.0, the main advantage of this interface being that it's sort off database independent. This is because it is provided through .Net database drivers.
More about GetSchema can be found here
All in all the INFORMATION_SCHEMA brings us a lot of power in terms of database information and can be used for a lot of different purposes.