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
- information_schema is a schema and available in all databases by default.
- In views like information_schema.columns, the column table_catalog will show database name and column table_schema will show the schema name.
- 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
- information_schema is a database that has information for all databases
- In views like information_schema.columns, the column table_catalog will be NULL and column table_schema will show the database_name.
- 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'