What is the Problem?
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.
Why is This a Problem?
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.
Using the Information Schema
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
COLUMNS in the database or find
This first query will return all of the tables in the database you are querying.
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
The second query will return a list of all the columns and tables in the database you are querying.
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
Or, you can also query for just the
COLUMNS from a specific table and return the column names from the specific table ‘Album’ in our database.
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Album'
With this next query you can find out whether or not there is a
TABLE in the data source that matches some kind of search parameters.
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Album' ) SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;
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
COLUMN in the data source that matches some kind of search parameters.
IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'Title' ) SELECT 'found' AS search_result ELSE SELECT 'not found' AS search_result;
INFORMATION_SCHEMA view in your data source can be a reliable way to determine what is in the data source while you build your queries.
For more information on the Microsoft SQL Server System Information Schema Views, please read more from their support docs.