When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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


    Read Other SQL Guru Questions


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article