April 26, 2012

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'


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "Information Schema - SQL Server vs MySQL"
  1. Vishal Pawar said...
    April 30, 2012 at 6:37 AM

    Nice pice of information
    I am really collecting all topics of this series ...
    Thanks a Lot

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions