Today's question comes from Keith:
Help!!!!! I am trying to write code that will do a
SELECTstatement 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?
In earlier version of SQL Server you could do a query directly to the
(For more information be sure to read: a past SQLGuru question on Determining the
Creation Dates of Tables, an article on Listing the Tables and Columns in a Database,
and an article on Using the
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.
You may not be able to see the |
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
SQL Server labels them
fourth field is
TABLE_TYPE and contains either
BASE TABLE or
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:
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.