Querying INFORMATION_SCHEMA

This post applies to both MySQL and Microsoft SQL Server as both these products allow you to query something called INFORMATION_SCHEMA.

What is INFORMATION_SCHEMA?

It is database metadata. In MySQL, there is a complete virtual database dedicated to it called “information_schema” and in Microsoft SQL Server, there are views which you can query, all prefixed with INFORMATION_SCHEMA in each database.

You can find a full list of the views by opening up the “System Views” node in Management Studio.

In MySQL, you would run the following to get information on tables in a database:
[sourcecode language=’sql’]SELECT * FROM information_schema.tables
WHERE table_schema = ‘YourDatabaseName’;[/sourcecode]
In SQL Server, the same would be:
[sourcecode language=’sql’]USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.tables;[/sourcecode]
The metadata is automatically updated by the database engine and it is not possible to update the data within but direct user initiated queries

Why use INFORMATION_SCHEMA?

Some of the things I would use it for would include:

• Finding out how many rows per table you have in your databases
• Finding out how large your tables and indexes are
• Finding out when a stored procedure was last updated
• Assessing index fragmentation (applies to MySQL, you would use a DMV in SQL Server called sys.dm_db_index_physical_stats or legacy DBCC SHOWCONTIG)
• Finding out which stored procedures contained certain queries (see below example)

A question I get asked as a DBA sometimes is how can I find a stored procedure containing a certain query. I point the developer at INFORMATION_SCHEMA.ROUTINES and to look in the “routine_definition” field for specific keywords, for example I would use this against SQL Server:
[sourcecode language=’sql’]USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_definition LIKE ‘{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}keyword{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}’;[/sourcecode]
In MySQL you can get this information too but it is not part of INFORMATION_SCHEMA and instead you have to query the mysql.proc table.

There are other ways to get this information, for example in SQL Server, you can load up the Object Explorer in Management Studio to view information on row counts, space used for data and indexes per table but I prefer writing queries to do this.

I think in part because it is a guilty pleasure of mine to query the server using T-SQL or command line tools in MySQL 🙂 and most importantly because there is more information to be had via this method.

For more information, simply start querying it and you will find a wealth of useful stuff inside. Or you can check out these links for both MySQL information_schema and SQL Server information_schema