Have you ever been in this situation? You’re the new developer for a database powered application and you have to figure out what tables contain a specific column? Or maybe you’ve got a legacy database that needs it’s stored procedures and user defined functions cleaned up? Or perhaps you’ve been assigned a unique database inventory task that requires you to report on the structure of your database, rather than the contents of your database. Besides cracking open your favorite SQL editor and clicking through your database by hand, you might find the data you need from your database’s INFORMATION_SCHEMA System View.
All of the RDMS systems I’ve worked with have built in System Views ( or tables ) that contain data about the structure and contents of your database. These views are there to help you with non data tasks. For the sake of this post we’re only going mention the INFORMATION_SCHEMA, which is the system view I use most in SQL Server, and MySQL. For your reference, here are links to INFORMATION_SCHEMA docs for four popular RDMS systems.
- SQL Server – http://msdn.microsoft.com/en-us/library/ms186778.aspx
- MySQL – http://dev.mysql.com/doc/refman/5.5/en/information-schema.html
- Oracle – http://download.oracle.com/docs/cd/E17952_01/refman-5.0-en/information-schema.html
- DB2 – http://www.tar.hu/sqlbible/sqlbible0100.html
If you work with SQL Server, you’ll be happy to know you can see all of the System Views in Management Studio by default.
System Views in the AdventureWorks database
If you work with MySQL, you can see the INFORMATION_SCHEMA database in MySQL Workbench, but you’ll have to enable it in Preferences first.
Edit > Preferences > SQL Editor > Check 'Show Metadata Schemata'
Now for the MySQL users, turning on the Metadata Schemata is not required in order to query those system tables. Assuming your user has appropriate permissions, you can always fire up Workbench and fire a query like this.
[sql]
SELECT *
FROM INFORMATION_SCHEMA.Columns
WHERE Column_Name LIKE ‘%user%’
[/sql]
MySQL has INFORMATION_SCHEMA tables too!
Now that you know about INFORMATION_SCHEMA, you should be happy to know this is part of the SQL-92 standard. Which means whatever sql scripts you write in MySQL, will most likely work with SQL Server as well. So go ahead and start querying your database, you’ll probably find uses for the INFORMATION_SCHEMA in your daily life really quickly.
I’m going to assume you’ve got the general idea here, so here are a few sql scripts that I’ve used over the years. The first three are informational queries, and the last three are stored procedures that generate .NET or Coldfusion code based off of a table name.
Find all tables with the column EmployeeID
[sql]
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘EmployeeID’
[/sql]
Which tables have an EmployeeID column?
How many tables have the word employee in the name
[sql]
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE LOWER(TABLE_NAME) LIKE ‘%employee%’
[/sql]
How many %employee% tables are in the DB?
How many SPROCs and UDFs does our database contain?
[sql]
SELECT s.SPROCs, f.UDFs
FROM
(
SELECT COUNT(ROUTINE_NAME) AS ‘SPROCs’, NULL AS ‘UDFs’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’
) AS s,
(
SELECT NULL AS ‘SPROCs’, COUNT(ROUTINE_NAME) AS ‘UDFs’
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘FUNCTION’
) AS f
[/sql]
How many stored procedures and user defined functions are in the DB?
The stored procedures listed below all accept an incoming table name, then reads data from INFORMATION_SCHEMA to generate code for the specified database table. Instead of listing the raw SQL, I’m just showing a sample result and allowing you to download the raw SQL sprocs.
Generate C# ADO.NET to VO Stored Procedure
Build custom C# ADO.NET to custom VO fillerup code
Generate .NET VO class Stored Procedure
Generate Coldfusion CFFunction with CFQUERY Stored Procedure
Generate Coldfusion CFFunction with CFQUERY for specified table name
I use these System Views almost daily, and there are a lot of scenarios where my job would take a lot longer to do if I didn’t know about this information. The INFORMATION_SCHEMA also feeds my passion to build code that makes code, just love it. Hopefully this nickel tour was enough information to help somebody out.
Eric Fickes's bloggy blog 