Check if SQL table exists

[MDA]

Hi all,

New challange for me... I am trying to figure out how to check if a table exists in a SQL server database using an ado connection.

I have syntax such as:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYTABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MYTABLE]... etc etc

However, I dont want to drop the table I just want to prompt a message to the user in VB that the table already exists.. I am not sure how to return something so that I know if it exists or not..

For Instance if exists then 1 otherwise 0. Then I can execute a message based on that result.

Any ideas on how this can be accomplished??

Thanks a million for everyones support.

Regards,

MDA

 

[NickISD]

How about :-

cSql = "SELECT name FROM sysobjects WHERE name = 'MyTable'"
Set rs = Db.OpenResultset( etc etc - make ADO recordset)
If Not (rs.EOF and rs.BOF) Then
'Table name exists, show user message
Else
'Table does not exist
Endif

Hope this helps.

Regards, Nick