All you wanted to know about the INFORMATION_SCHEMA (MsSQL) and then some :)

Posted Tuesday, July 11, 2006 5:59 PM by Mischa Kroon

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
Database catalog
Owner schema
Object object
user-defined data type domain

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.

Filed under: ,

Comments

# re: All you wanted to know about the INFORMATION_SCHEMA (MsSQL) and then some :)

Thursday, July 13, 2006 1:40 AM by Jan Schreuder

Or you can build your own CommandBuilder object. I use da.MissingSchemaAction = MissingSchemaAction.AddWithKey in my commandbuilder object to get all the metadata I need to build CRUD command objects.  (see: http://bloggingabout.net/blogs/jschreuder/archive/2006/07/12/12952.aspx)

Thanks for the reference info

# re: All you wanted to know about the INFORMATION_SCHEMA (MsSQL) and then some :)

Thursday, July 13, 2006 5:27 AM by Mischa Kroon

Thanks, but this solution probably isn't for me.

I will have a look in a while though.

# re: All you wanted to know about the INFORMATION_SCHEMA (MsSQL) and then some :)

Friday, July 14, 2006 1:08 AM by Marc Jacobi

The bullet proof section is really cool. Thats any idea I had for a long time now (but never got around to prototype it ;-). I figured you would write a DAL that would use DLL to dynamically create the database schema it needs at runtime. This is one step further than the article describes and you run into all kinds off tricky stuff. I remember that I wished for a really fast, small, binary and stripped down database and API. You could dynamically optimize the database (creating indexes, stored procs etc) as it runs in the production environment.

# re: All you wanted to know about the INFORMATION_SCHEMA (MsSQL) and then some :)

Monday, July 17, 2006 2:32 PM by Mischa Kroon

@Marc

Then you will probably want to have a look at Og.
This does most of what you are discribing.

It is running on Ruby though, and is part of the nitro framework:

http://nitroproject.org

Leave a Comment

(required) 
(required) 
(optional)
(required)