How to check if a table exists? 3

[ReportingAnalyst]

Hi,

Before dropping a table, I would like to see if the table exists. Only if it exists, then I would like to drop it.

Thanks.

 

[SQLDenis]

This is if your table is named Table1, change the table name for your use

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[gmmastros]

If Exists(Select * From Information_Schema.Tables Where Table_Name = '[!]YourTableName[/!]')
  Begin
    Drop Table [!]YourTableName[/!]
  End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

IF exists(select * from sysobjects where name= table name)
DROP table

- Paul
- Database performance looks fine, it must be the Network!

 

[ReportingAnalyst]

Thanks George and Dennis.

I used Dennis's approach, but I just check for the name against the table.

 

[ptheriault]

I like Georges Best!

- Paul
- Database performance looks fine, it must be the Network!

 

[gmmastros]

Actually, Denis's code is better. Views are included in information_schema.tables, which could cause problems with my code.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

yes but If you are using schema binding in your view then it wouldn't, correct?

- Paul
- Database performance looks fine, it must be the Network!

 

[SQLDenis]

>>yes but If you are using schema binding in your view then it wouldn't, correct?

but you can still drop the view

create table dbo.a (id int)
go

create view b with schemabinding
as
select id from  dbo.a 

drop table a

Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'a' because it is being referenced by object 'b'.


However this is fine

drop view b

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[SQLDenis]

>>Actually, Denis's code is better. Views are included in information_schema.tables, which could cause problems with my code.

you can always add and TABLE_TYPE ='BASE TABLE'

Select * From Information_Schema.Tables Where Table_Name = 'YourTableName'
and TABLE_TYPE ='BASE TABLE'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ESquared]

And despite the fact that query analyzer does it with the sysobjects table, using the information_schema views is recommended because they should stay the same between versions of SQL Server and the sysobjects table may not.

 

[gmmastros]

I used to think the same thing.

In SQL 2000, you can get a list of databases on the server with:
select * from information_schema.schemata

Try that on SQL 2005 and you get completely different data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

>>In SQL 2000, you can get a list of databases on the server with: select * from information_schema.schemata

that's because SQl server 2000 didn't have Schema's and the implementation was completely wrong

The correct version is the 2005 version



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[ESquared]

okay, the information_schema views are less likely to change than the sysobjects table.