The OBJECTPROPERTY() function works with current database. You've to select proper database in your dynamic T-SQL because it runs in it's own context.
set @cmd = 'USE ' + @service_name +
' if exists (select * from sysobjects
where id = object_id(N'''+ @table +''')
and OBJECTPROPERTY(id, N'+'''IsUserTable'''+') = 1)
drop table '+ @table +'
else
print '+'''Table does not exist ' +@table + ''' '
EXEC (@cmd)
>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)
>
--sb--