Information Schema - SQL Server vs MySQL

Continuing my series on how same things can be done differently in SQL Server and MySQL, in this post, we will see how Information Schema is used in SQL Server vs MySQL.

To view information about a column for a table  or list of tables available in the database or primary and foreign key information etc. etc. we can use information_schema views like - information_schema.tables, information_schema.columns and so on. This option is available in both SQL Server and MySQL. However there are some differences in their usage.

In SQL Server

1. information_schema is a schema and available in all databases by default.
2. In views like information_schema.columns, the column table_catalog will show database name and column table_schema will show the schema name.
3. By default, it shows the result for the current database. If you want to show results for a different database, you need to qualify a database name like db_name.information_schema.columns

In MySQL

1. information_schema is a database that has information for all databases
2. In views like information_schema.columns, the column table_catalog will be NULL and column table_schema will show the database_name.
3. By default it shows the result for all the databases. If you want to show results for a specific database, you need to filter  on
   the column table_catalog ex WHERE table_catalog='db_name'

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan