|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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 Show quote "Les Stockton" <LesStock***@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? > In general, best means, effective and elegant.
Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > 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" <LesStock***@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? > > > > > > 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? 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. Show quote "Les Stockton" wrote: > What's the best way to test for the existence of a table programmatically? > 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 -- Show quotebest 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? Show quote "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: > > > What's the best way to test for the existence of a table programmatically? > > SELECT @@VERSION
EXEC sp_dbcmptlevel 'db_name' Show quote "Les Stockton" <LesStock***@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: > >> 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? >> > 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) -- Show quoteAndrew J. Kelly SQL MVP "Chandra" <chan***@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: > >> What's the best way to test for the existence of a table >> programmatically? >> 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. Show quote "Les Stockton" <LesStock***@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? >  |
|||||||||||||||||||||||
Other interesting topics