How do you see if a table already exists in a database?

[bob]

I am writing some code to create new tables in a SQL database. However,
I don't want to try to create a table if it already exists. How can I
test beforehand to see if a particular named table already exists in
the database, without actually trying to read records into a dataset?

Thanks.

 

[Guest]

Something like this:

IF NOT EXISTS (
SELECT *
FROM MyDatabase.dbo.sysobjects
WHERE Name='MyTable'
AND TYPE='u')
BEGIN
CREATE TABLE ...
END

 

[bob]

Well, I was hoping that the SqlConnection object would have some
collection like "Tables" or something. Is there really no such
collection?

 

[CT]

The only Tables collection exists in the DataSet object and it is only
filled on request using a DataAdapter, which wouldn't suit your requirement
I'm sure. tlkerns suggestion is a good one with SQL Server.

 

[Oenone]

Something like this:

A much better way to implement this IMO is:

\\\
if not exists(
select *
from INFORMATION_SCHEMA.tables
where TABLE_NAME = 'MyTable')
begin
create table ...
end
///

This is using a well-documented ANSI/ISO standard view to retrieve data on
the schema rather than hacking around in system tables and using "magic"
values. Works in SQL Server 2000 and later (and in many non-SQL Server DBMSs
too).

I continue to be very surprised to see how many people turn to system tables
to retrieve schema data when these views have been around for years and
provide all the same data in a much more easily obtainable and future-proof
fashion.

 

[bob]

Hi.

As I understand your example, this would have to be placed into an
sqlcommand and executed with the SqlExecuteNonQuery method. But it
would then CREATE the table if it didn't exist. Whereas, I only want to
find out if the Table exists, I don't want to necessarily create it.

Can you execute an SQL command and get it to return True or False,
depending on whether the table exists? How do you handle the "result"
of such a query?

Thanks.

 

[C-Services Holland b.v.]

Hi.

As I understand your example, this would have to be placed into an
sqlcommand and executed with the SqlExecuteNonQuery method. But it
would then CREATE the table if it didn't exist. Whereas, I only want to
find out if the Table exists, I don't want to necessarily create it.

Can you execute an SQL command and get it to return True or False,
depending on whether the table exists? How do you handle the "result"
of such a query?

Thanks.

If you just execute this:
SELECT *
FROM MyDatabase.dbo.sysobjects
WHERE Name='MyTable'
AND TYPE='u'

It will give you a resultset. You can check the recordcount and see if
it's > 0. if >0 then the table exists.

 

[bob]

THANKS!!

If you just execute this:
SELECT *
FROM MyDatabase.dbo.sysobjects
WHERE Name='MyTable'
AND TYPE='u'

It will give you a resultset. You can check the recordcount and see if
it's > 0. if >0 then the table exists.

 

[Oenone]

If you just execute this:
SELECT *
FROM MyDatabase.dbo.sysobjects
WHERE Name='MyTable'
AND TYPE='u'

....or, once again, you could go with the ANSI/ISO approach instead of
hacking around in system tables, and use the same approach with this SQL:

\\\
select *
from INFORMATION_SCHEMA.tables
where TABLE_NAME = 'MyTable'
///