Does a table exist? 1

[innmedia]

Hi all,

Just wondering the best way to tell if a table exists before writing data to it. This would be from an Access front end. Is there a particular stored proc? Or is it a query into one of the system tables?

Thanks!

KB

 

[donutman]

You could write a SP that returned the count of the number of table objects that matched the table name in question.

Select Count(Id) From sysObjects 
   WHERE Name='YourTableName' and xType='U'

I believe the 'U' restricts the count to tables rather than SPs or views, etc. BTW, this is not how you would write the SP. Ask if you need help.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[innmedia]

Donutman -

Perfect! Thanks, it's just what I needed.

 

[JohnDTampaBay]

Use the Information_Schema views to return information on database objects. They are visible in the master database, but exist in every database.

IF EXISTS(SELECT * FROM Information_Schema.Tables
          WHERE Table_Name = 'SomeTable')
   DROP TABLE SomeTable

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)