Drop table in another database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Drop table in another database

Author

Topic

dbchick
Starting Member

1 Post

Posted - 2008-10-27 : 18:14:01

Hi,

I am attempting to write a stored procedure to drop a table, if it exists.

My problem is that my stored procedure is in a different database (on the same server) and I can't seem to force it to look at database I'm working on.

Here is my code:

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

The select statement is reading from the current database, so the if condition evaulates to FALSE and it doesn't drop the table.

I can get it to work with a USE statement pointing to the correct db, but it won't allow me to create a stored procedure with a USE statement.

What am I missing?

Any help is appreciated. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-27 : 18:16:41

You are having issues with OBJECT_ID and OBJECTPROPERTY functions. They act on the current database. To get around this, you can use sp_executesql to do this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 18:34:14

Why don't you use:

WHERE name = '[TABLENAME]' and xtype = 'U'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources