Drop Table

[mayu03]

From a sp in database1 I need to drop a table in database2.
How do I do this? Right now my statement can't see that table located in different db.
drop table [dbo].

 

[MatthewPeters]

try this

drop table databaseName.dbo.tableName

Matt

 

[mayu03]

Here is my statement, however it is not working.
if exists (select * from dbo.sysobjects where id = object_id(N'[prov].[dbo].[Prov]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [prov].[dbo].[Prov]

 

[nigelrivett]

OBJECTPROPERTY(id, N'IsUserTable') = 1)
works in the current database.
You also need to reference sysobjects in the remote database.

You can do this by using xtype on sysobjects

if exists (select * from Prov.dbo.sysobjects where id = object_id(N'[prov].[dbo].[Prov]') and xtype = 'U')
drop table [prov].[dbo].[Prov]


You could also use sp_executesql to execute in the context of the remote database


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




======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.