SQL Server INFORMATION_SCHEMA Views | See if a Table Exists

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 TABLES and COLUMNS in the database or find TABLES and COLUMNS.

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;

Utilizing the 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.