The SQL Guru Answers your Questions...
Today's question comes from Keith:
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?
|
In earlier version of SQL Server you could do a query directly to the sysobjects table.
(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 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.
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.
Answered by SQLGuru graz - SQLTeam.com
