How to find a table existed or not?

Question

Post reply

How to find a table existed or not?

y Zhang

SSC Enthusiast

Points: 130
More actions
January 11, 2008 at 6:31 pm

#127503

In SQL 2000, there is a system table called sysobjects that contains table name in a database, which gives us info about a table existed or not. In SQL 2005, the system table is no there anymore. Is there a better way in SQL 2005 to detect table existed, ideally, from C# code?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Even in SQL Server 2000, there is no need to engage any systems tables to figure out if an object exists...

IF OBJECT_ID('someobjectname','xtype') IS NOT NULL

... object exists ...

ELSE

... object does not exist ...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

y Zhang SSC Enthusiast Points: 130 More actions · Answer 2

Thank you Jeff.

If the table name is already known when one designs the stored procedure, using T-SQL's IF ELSE would do the job.

However, in my case, the table name string is dynamically created and passed from an C# application and changed daily. As far as I know (I may be wrong), T-SQL based stored procedures would not take a table name as an input string parameter. Therefore, instead of using T-SQL based stored procedure, I had to do some coding in the C# side of the application. Using SQL 2000 system tables' info is not neat but did the job using C#. That is why I have the difficulty on SQL 2005. Do I miss something here?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Yes... use "Dynamic SQL" to accomplish this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

DECLARE @TblName VARCHAR(50)

SET @TblName = 'MyTable' -- can be a parameter also

IF (SELECT OBJECT_ID(@TblName,'u')) IS NOT NULL

PRINT 'Table Exists'

ELSE

PRINT 'Table Not Exists'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

sys.objects or information_schema.tables

"Keep Trying"

kimjongyouly SSC Enthusiast Points: 144 More actions · Answer 6

--1. using function of object_id

if object_id('object_name') is not null begin

print 'Exist'

end else begin

print 'Not exist'

end

--2. using sys.objects & exists function

if exists(select 1 from sys.objects where name 'object_name'') begin

print 'exists'

end else begi

print 'not exists'

end

--3. using stored procedures

-- ex) sp_tables, sp_stored_procedures

exec dbo.sp_tables 'table_name'

if @@rowcount > 0 begin

print 'exists'

end else begin

print 'not exists'

edn

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Jeff Moden (1/14/2008)
Yes... use "Dynamic SQL" to accomplish this...

Ack... not enough coffee... sorry.

Gail's (GilaMonster) post is correct for this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krayknot SSCertifiable Points: 7998 More actions · Answer 8

Use the script below

IF EXISTS (SELECT 1

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE='BASE TABLE'

AND TABLE_NAME='tablename')

SELECT 'tablename exists.'

ELSE

SELECT 'tablename does not exist.'

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

krayknot (10/16/2008)
Use the script below
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='tablename')
SELECT 'tablename exists.'
ELSE
SELECT 'tablename does not exist.'

Why would you query a view when the system function "OBJECT_ID" that Gail used works?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

or - just build the table using a predefined name, and use sp_rename. No dynamic SQL that way.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?