How do I know if a table exists in a SQL Server database?

I need to do some processing but only if a specific table 'table1' exists in the SQL Server database. How can I check if this 'table1' exists using either ADO.Net code or SQL Server query?
Query the system table sysobjects first. If you have ever scripted a table in Enterprise Manager, it does just that to make sure it doesn't try to create a table that already exists. Here is a sample:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Account]
|||Rather than directly querying the sysobjects table, query the INFORMATION_SCHEMA.TABLES view:
IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'table1')
BEGIN
-- do some processing
END
See this previous post for an explanation of why querying the sys* tables is not a good idea:http://forums.asp.net/960368/showpost.aspx
|||

I was thinking of posting a reply to this thread. I always do this type operation with the code shown below.

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'table_name' AND type = 'U')
DROP TABLE table_name
The above code as shown generated exactly for you autiomatically from Query Analyzer. So I believe this is the way Microsoft does it and best way(?).
How do we know if this is generated by query analyzer? Try this: open QA, then on the drop of the first icon i.e New Query (below the file menu) and select Create Table > Create table basic template. That will give you a template to work with the above code.
I would love to see a reply from Terri on this.
Thanks

|||What Terri is saying is the System tables are Microsoft property you don't use them for anything unless there is no alternative. This is something I have been saying for a long time don't use Microsoft properties in your code because those tables get changed and moved, creating errors in your code. The System tables almost doubled in number between SQL Server 7.0 and 2000 and are removed in SQL Server 2005. Hope this helps.|||

Caddre wrote:

What Terri is saying is the System tables are Microsoft property you don't use them for anything unless there is no alternative. This is something I have been saying for a long time don't use Microsoft properties in your code because those tables get changed and moved, creating errors in your code. The System tables almost doubled in number between SQL Serve 7.0 and 2000 and are removed in SQL Server 2005. Hope this helps.

Then why the code auto generated by Query Analyzer has this type of coding?|||

musa wrote:

Then why the code auto generated by Query Analyzer has this type of coding?

Because the code generated by Query Analyzer is generated for use in SQL Server 2000. The code generated from the SQL Server 2005 version of the tool will be different. As Caddre said, that's the point -- Microsoft does not guarantee backward-compatibility for the sys* tables. Please see the link I provided which links to a discussion a few SQL Server MVPs had on this topic.
The INFORMATION_SCHEMA views are supposed to be backwards-compatible, so they are the best way to query for schema-related information.|||

tmorton wrote:

musa wrote:

Then why the code auto generated by Query Analyzer has this type of coding?

Because the code generated by Query Analyzer is generated for use in SQL Server 2000. The code generated from the SQL Server 2005 version of the tool will be different.

Terri,
If I generate script from enterprize manager for a table that also has the same type of coding. I haven't looked at SQL 2005, so I don't know if this coding will be different.
Anyway, good to know the compatibility issue. Thanks|||I agree that you should use Information_schema if you are doing selects that will be around after the fact (putting something in a stored proc for example). But if you are running something once, and then discarding it (creating a table, just want to get some information, etc) then I dont see any issue with using the system tables. For example, recently I needed to update the owner of all of my DTS packages. It was much easier to query sysdtspackages then to find out the correct view to use, the correct field names to modify and such. Especially since I only needed to do this once, and wont have to again.
Just my two cents.

Nick|||

nick-w wrote:

But if you are running something once, and then discarding it (creating a table, just want to get some information, etc) then I dont see any issue with using the system tables.

Well absolutely, if it's easier to use the system tables for something that is once-and-done, of course that is fine (which is not always the case, at least not for me because I could be bothered trying to remember the different field values and sys* table relationships). But the question at hand deals with part of an application that is going to have some sort of longevity.|||The Query Analyzer used Enterprise manager to generate the code which like the System tables is Microsoft property. The question is have you seen code using System tables in SQL Server books and the answer is no, SQL developers don't use System tables. I hope I am clear about the main points. Hope this helps.|||

tmorton wrote:

Well absolutely, if it's easier to use the system tables for something that is once-and-done, of course that is fine (which is not always the case, at least not for me because I could be bothered trying to remember the different field values and sys* table relationships). But the question at hand deals with part of an application that is going to have some sort of longevity.

I guess if I would have read what was being asked for I would have realized that...opps sorry :)
Nicksql