does a table exist?

Does anyone know if you can check if an object (eg a table) exists. I am
having trouble with procs that drop tables before creating them. Other users
get the message, "cannot drop table tblTest ... doesn't exist in the system
catalog".
I need to do something like,
If tblTest exists
Drop tblTest
...
then recreate the table
ThanksNickl wrote:
> Does anyone know if you can check if an object (eg a table) exists. I am
> having trouble with procs that drop tables before creating them. Other use
rs
> get the message, "cannot drop table tblTest ... doesn't exist in the syste
m
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks
>
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Say I want to check if table "Departments" exists (from the "Generate
SQL Scripts" option in Enterprise Manager):
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Departments]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Departments]
Just substitute your table name (and owner [dbo]?) for Departments.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQgA9TYechKqOuFEgEQIBvwCgyWX4oG3wDVTW
URVNdrLkAz6/WKUAnRak
h8BmLTTPTkyE2MvNYl2mkVGH
=UYde
--END PGP SIGNATURE--|||Hello Nickl,
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblTest'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> Does anyone know if you can check if an object (eg a table) exists. I
> am
> having trouble with procs that drop tables before creating them. Other
> users
> get the message, "cannot drop table tblTest ... doesn't exist in the
> system
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks|||Or...
if object_id('your_tb','U') is not null
drop table your_tb
-oj
"Nickl" <Nickl@.discussions.microsoft.com> wrote in message
news:4A514243-6D69-427F-8F0B-2B6D1B4481E3@.microsoft.com...
> Does anyone know if you can check if an object (eg a table) exists. I am
> having trouble with procs that drop tables before creating them. Other
> users
> get the message, "cannot drop table tblTest ... doesn't exist in the
> system
> catalog".
> I need to do something like,
> If tblTest exists
> Drop tblTest
> ...
> then recreate the table
> Thanks
>|||Thanks everyone, good methods all. I really like yours oj, it looks very bri
ef
NIck Lindner
"oj" wrote:

> Or...
> if object_id('your_tb','U') is not null
> drop table your_tb
> --
> -oj
>
> "Nickl" <Nickl@.discussions.microsoft.com> wrote in message
> news:4A514243-6D69-427F-8F0B-2B6D1B4481E3@.microsoft.com...
>
>