DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Home All Articles Querying INFORMATION_SCHEMA

Querying INFORMATION_SCHEMA

Posted on July 14, 2012 Written by Andy Hayes 2 Comments

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:

SELECT * FROM information_schema.tables
WHERE table_schema = 'YourDatabaseName';

In SQL Server, the same would be:

USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.tables;

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:

USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_definition LIKE '{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}keyword{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}';

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

Related Posts:

  • How to Take a Backup of a Table in MySQLHow to Take a Backup of a Table in MySQL
  • How to Setup MySQL Master Master ReplicationHow to Setup MySQL Master Master Replication
  • How to set up MySQL Replication TutorialHow to set up MySQL Replication Tutorial
Share this...
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
Pin on Pinterest
Pinterest
Share on Reddit
Reddit
Share on StumbleUpon
StumbleUpon
Share on Tumblr
Tumblr
Buffer this page
Buffer

Filed Under: All Articles, MySQL Administration Tagged With: mysql, sql server

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Comments

  1. sandy.zheng says

    August 22, 2012 at 7:05 am

    Thanks for sharing.

    A question I get asked as a DBA sometimes is how can I find a stored procedure containing a certain query.

    This is another method :

    use ReportServer$SQLSERVER

    select a.name,a.[type],b.[definition] from sys.all_objects a,sys.sql_modules b
    where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in (‘P’,’V’,’AF’) and b.[definition] like ‘{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}Samply{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}’
    order by a.[name] asc

    Reply
    • Andy Hayes says

      August 26, 2012 at 8:34 am

      Hi Sandy

      Thanks for your comment and sql query. Very useful.

      All the best

      Andy

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

CAPTCHA
Refresh

*

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • How to move tempdb
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2019’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close