Checking if a Table Exists

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  August 15, 2006 at 10:45 am

  #62027

  Hi All, I have an insert statement in the form of TQL, but before i want to execute it, I need a check to see if the table exists first, I know how to write this clearly, but for some reason, I am geting confused.

   

  Please have a look at the code below, I can go the easy way of doing this:

  IF Exists (Select * from sysobjects where name  = @Table)

  But the problem is that once we move onto 2005, if the table is under a different schema, it would be a problem.

  Thats why i am checking this way

  NOT WORKING

  DECLARE @Tsql = 'SQL insert statement'

  DECLARE @TABLE VARCHAR(2000)

  SET @TABLE = 'TEST'

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + @TABLE + '])' and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  Exec @error = sp_executeSQL @Tsql

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  August 15, 2006 at 11:23 am

  #655116

  If you want compatibility you might also look at querying INFORMATION_SCHEMA.tables. That's the same between 2000/2005.

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  August 15, 2006 at 7:15 pm

  #655185

  Stop looking at tables for this... use the built in functions...

  IF OBJECT_ID('databasename.ownername.objectname') IS NOT NULL

  DROP objecttype ownername.objectname

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