Using the Information Schema Views

By Bill Graziano on 3 August 2000 | Tags: Administration

Keith writes "Help!!!!! I am trying to write code that will do a select statement from a table (if it exists). I do not know if the table exists or not however. Is there a SQL statement that will determine if the table exists or not? Thank you, Keith" Yes Keith, there certainly is and it's pretty easy to do. Two lines of code.

In earlier version of SQL Server you could do a query directly to the sysobjects table. This table had one entry in it for each database object such as tables, views, stored procedures, etc. You could query it just like any other table. You actually still can but there is a better way.

Note: You may not be able to see the sysobjects table in Enterprise Manager. When you register a server in Enterprise Manager, one of the options is "Show System Databases and System Objects." When this option is cleared it will hide the master, model, msdb and tempdb databases. It will still show pubs and Northwind since those are user databases. It will also hide system tables such as sysobjects and syscomments. There are 18 system tables and 20 system views. Even if these tables and views are hidden from Enterprise Manager you can still query them directly in Query Manager or an ASP page.

One word of advice: DON'T UPDATE system tables or views directly without knowing EXACTLY what you are doing! Just don't. Ever.

SQL Server 7.0 added something called Information Schema views to the list of system objects. These views were added to conform to the SQL-92 standards. Basically these views provide a consistent view of certain system objects such as tables and views. Using these views will spare you any problems if Microsoft changes the structure or functionality of the sysobjects table in future versions.

We are concerned with a view called TABLES. It lists all the tables and views in the database including system tables. It even lists itself. We name an object in SQL Server using the syntax database.owner.object. In Information Schema terminology, database is called Catalog, owner is called Schema and object is called Object. These are the first three fields in the TABLES view. SQL Server labels them TABLE_CATALOG, TABLE_SCHEMA and TABLE_NAME. The fourth field is TABLE_TYPE and contains either "BASE TABLE" or "VIEW".

So Keith, back to your question. If you want to select the first record from a table called Table8 but only if it exists, you might write something like this:

if EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = 'Table8')
    Select Top 1 * from Table8

Okay, I threw two extras in at the end and they are both in capital letters in the query. The first is the EXISTS keyword. Basically the IF statement will evaluate to true if the query following EXISTS returns one or more records. It doesn't actually run the entire query, it just makes sure there is at least one record to return. This is a very quick way to check for the existance of records.

The second is INFORMATION_SCHEMA. Whenever you refer to one of the Information Schema views you need to qualify it with the owner INFORMATION_SCHEMA. There are also Information Schema views for columns, domain constraints, referential constraints and a few others. You can find more details in Books Online. Hope this helps.