When writing queries for a database you might be new to, or one that changes often, you might want to run a quick check to find all the tables in a specific database, or the columns in the database, or to search if table or column exists. Understanding the schema and what tables are in it help to write efficient SQL and helps avoid running queries multiple times just to see if the schema name or column name is correct. This tutorial will help solve these problems. Querying the metadata on a data source is the easiest way to
determine the makeup of a table if you don’t have an understanding of it already. Microsoft SQL Server provides an information schema view as one of several methods for obtaining this metadata. As their support documentation states, “Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although
significant changes have been made to the underlying system tables.” We will be using a couple of the views in the information schema in order to run queries that help determine the makeup of tables in the data source. To Show the This first query will return all of the tables in the database you are querying.
The second query will return a list of all the columns and tables in the database you are querying.
Or, you can also query for just the
With this next query you can find out whether or not there is a
The query will return the word ‘found’ if the table ‘Album’ exists in our database. Now to take it a littler
further, you can use this query to find out whether or not there is a
Utilizing the For more information on the Microsoft SQL Server System Information Schema Views, please read more from their support docs. This article provides information about the information_schema database in MySQL. About the information_schema databaseThe information_schema database is a virtual database that MySQL uses to store information about your databases and other settings. For example, you can query the information_schema database to obtain information about character sets, statistics, and much more. Although you can query tables in the information_schema database, you cannot modify them. You also cannot delete the information_schema database. To see an example of the information_schema database in action, please see this article. More InformationFor more information about the information_schema database in MySQL, please visit http://dev.mysql.com/doc/refman/5.5/en/information-schema.html. |