I am trying to drop tables that exist in a database (Making tables based on metadata and somtimes we get dupes on table names )
This code:
set @foo = ' use ' + @service_name
print @foo
exec ( @foo )
set @cmd = 'if exists (select * from '+@service_name+'.dbo.sysobjects
where id = object_id(N'''+ @table +''')
and OBJECTPROPERTY(id, N'+'''IsUserTable'''+') = 1)
Begin
drop table '+ @table +'
End
else
begin
print '+'''Table does not exist ' +@table + ''' end'
print @cmd
exec (@cmd)
Fails to see a table??
Here is the print(s) with the error:
use DM_SLEEP_TEST
if exists (select * from DM_SLEEP_TEST.dbo.sysobjects
where id = object_id(N'[ DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ] ')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
drop table [ DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ]
End
else
begin
print 'Table does not exist [ DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ] ' end
Table does not exist [ DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ]
CREATE TABLE [ DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ] ( [Onset Date_B ] SMALLDATETIME NULL )
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named ' DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ' in the database.
But that code in a second window will work and Drop the Table???
and I see:
"Table does not exist [ DM_SLEEP_TEST.dbo.TBL_ACTIVE_ADVERSEEVENTS ] "
Any ideas???
__Stephen