best way to determine if a table exists?

What's the best way to test for the existence of a table programmatically?What does "best" mean? Here are some ideas, but in general, asking for the
"best" way to do something is kind of like asking what is the "best" car for
my wife. You kind of have to know her tastes, driving ability, commute,
budget, priorities, etc. to really know what is "best"...
http://www.aspfaq.com/2458
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:0519D8FF-D208-44AE-9082-317EFF7D9782@.microsoft.com...
> What's the best way to test for the existence of a table programmatically?
>|||Select OBJECT_ID(<Tablename> )
--But this doesn′t check for the table type
Select * from INFORMATION_SCHEMA.TABLES Where Table_name = <TableName> And
Table_Type = 'BASE TABLE'
--That queries the INformation Schema Views escpecially for the tables in
that case.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Les Stockton" wrote:

> What's the best way to test for the existence of a table programmatically?
>|||In general, best means, effective and elegant.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "best" mean? Here are some ideas, but in general, asking for th
e
> "best" way to do something is kind of like asking what is the "best" car f
or
> my wife. You kind of have to know her tastes, driving ability, commute,
> budget, priorities, etc. to really know what is "best"...
> http://www.aspfaq.com/2458
>
> "Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
> news:0519D8FF-D208-44AE-9082-317EFF7D9782@.microsoft.com...
>
>|||> In general, best means, effective and elegant.
Okay, fair, but I don't think it's fair to suggest that's what best means
for everyone (in other words, it's not something I'm willing to assume,
every time someone asks for the best way to do anything).
I'm not sure I understand "effective" -- does this mean "correct"? If so, I
don't think you'll find many "solutions" to the problem that aren't
"effective."
Also, "elegant" is very subjective, IMHO.
Have you looked at http://www.aspfaq.com/2458 ? Do you find any of those
solutions more effective or elegant than any others?|||Hi
Did u check this:
IF EXISTS (SELECT top 1 * FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[test_audit]')
AND OBJECTPROPERTY(id, N'IsTable') = 1)
print 'Table available'
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Les Stockton" wrote:

> What's the best way to test for the existence of a table programmatically?
>|||Okay, how's a good way to test to see if I'm running under 2000 or version 7
?
Some of my users are running MSDE and am curious if the versions would be
the same?
"Chandra" wrote:
> Hi
> Did u check this:
> IF EXISTS (SELECT top 1 * FROM sysobjects
> WHERE id = OBJECT_ID(N'[dbo].[test_audit]')
> AND OBJECTPROPERTY(id, N'IsTable') = 1)
> print 'Table available'
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Les Stockton" wrote:
>|||SELECT @.@.VERSION
EXEC sp_dbcmptlevel 'db_name'
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:C025CB85-046C-4D28-BAA9-DE76EEBF9B16@.microsoft.com...
> Okay, how's a good way to test to see if I'm running under 2000 or version
> 7?
> Some of my users are running MSDE and am curious if the versions would be
> the same?
> "Chandra" wrote:
>|||I would stay away from accessing the system tables directly when ever
possible. I like this approach:
IF OBJECT_ID('[dbo].[test_audit]') IS NOT NULL
AND OBJECTPROPERTY(id, N'IsTable') = 1)
Andrew J. Kelly SQL MVP
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:9D1DD2D0-DE2D-42BD-91EC-6C5B85096737@.microsoft.com...
> Hi
> Did u check this:
> IF EXISTS (SELECT top 1 * FROM sysobjects
> WHERE id = OBJECT_ID(N'[dbo].[test_audit]')
> AND OBJECTPROPERTY(id, N'IsTable') = 1)
> print 'Table available'
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Les Stockton" wrote:
>|||This is what I do:
USE pubs
GO
DECLARE @.tableName sysname SET @.tableName = N'employee'
IF OBJECTPROPERTY(OBJECT_ID(@.tableName), N'IsTable') = 1
PRINT 'YES'
ELSE
PRINT 'NO'
This works because OBJECT_ID returns NULL if the table doesn't exist,
OBJECTPROPERTY returns NULL if the id is NULL, and (NULL = 1) is not TRUE.
"Les Stockton" <LesStockton@.discussions.microsoft.com> wrote in message
news:0519D8FF-D208-44AE-9082-317EFF7D9782@.microsoft.com...
> What's the best way to test for the existence of a table programmatically?
>