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

13 Jun 2006 9:36 PM

bob@datasync.com

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.

14 Jun 2006 1:17 AM

tlkerns

Something like this:

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

Show quoteHide quote

"b**@datasync.com" wrote:

> 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.
>
>

14 Jun 2006 9:52 AM

bob@datasync.com

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

tlkerns wrote:
Show quoteHide quote

> Something like this:
>
> IF NOT EXISTS (
> SELECT *
> FROM MyDatabase.dbo.sysobjects
> WHERE Name='MyTable'
> AND TYPE='u')
> BEGIN
> CREATE TABLE ...
> END
>
> "b**@datasync.com" wrote:
>
> > 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.
> >
> >

14 Jun 2006 10:03 AM

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.

--
Carsten Thomsen
Communities - http://community.integratedsolutions.dk
---------
Voodoo Programming: Things programmers do that they know shouldn't work but
they try anyway, and which sometimes actually work, such as recompiling
everything. (Karl Lehenbauer)
---------
<b**@datasync.com> wrote in message

Show quoteHide quote

news:1150278757.773377.147150@y41g2000cwy.googlegroups.com...
> Well, I was hoping that the SqlConnection object would have some
> collection like "Tables" or something. Is there really no such
> collection?
>
>
> tlkerns wrote:
>> Something like this:
>>
>> IF NOT EXISTS (
>> SELECT *
>> FROM MyDatabase.dbo.sysobjects
>> WHERE Name='MyTable'
>> AND TYPE='u')
>> BEGIN
>> CREATE TABLE ...
>> END
>>
>> "b**@datasync.com" wrote:
>>
>> > 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.
>> >
>> >
>

14 Jun 2006 3:48 PM

Oenone

tlkerns wrote:

> 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.

--

(O)enone

16 Jun 2006 2:17 AM

bob@datasync.com

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.

Oenone wrote:
Show quoteHide quote

> tlkerns wrote:
> > 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.
>
> --
>
> (O)enone

16 Jun 2006 1:07 PM

C-Services Holland b.v.

b**@datasync.com wrote:

> 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.

--
Rinze van Huizen
C-Services Holland b.v

16 Jun 2006 8:21 PM

bob@datasync.com

THANKS!!

C-Services Holland b.v. wrote:
Show quoteHide quote

> b**@datasync.com wrote:
> > 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.
>
>
> --
> Rinze van Huizen
> C-Services Holland b.v

17 Jun 2006 9:03 AM

Oenone

C-Services Holland b.v. wrote:

> 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'
///

--

(O)enone