sysobject
in [Programming SQL]
|
Prev: Check constraint with a scalar UDF not working
Next: How Can I Know last time was activity on a database
From: GW on 14 Apr 2010 05:19 hi, need help, I have two databases. I have lines in SP running in Database A of which will check table existence in Database B but it did not find any table even the said table exists in Database B. Is this possible or did I miss something? The following failed:- if exists ( select * from dbo.sysobjects where id = object_id(N'[DATABASE_B].[dbo].[TABLE_A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [DATABASE_B].[dbo].[TABLE_A] TQVM.
From: Uri Dimant on 14 Apr 2010 05:31 GW I would check it in the below way if object_id('dbname.dbo.tblname') is not null do something "GW" <GW(a)discussions.microsoft.com> wrote in message news:7DD62CBF-86E4-4AAA-AE57-222CF775A1DB(a)microsoft.com... > hi, > > need help, > > I have two databases. I have lines in SP running in Database A of which > will > check table existence in Database B but it did not find any table even the > said table exists in Database B. Is this possible or did I miss something? > > The following failed:- > > if exists ( select * from dbo.sysobjects where id = > object_id(N'[DATABASE_B].[dbo].[TABLE_A]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > > drop table [DATABASE_B].[dbo].[TABLE_A] > > TQVM.
From: Tibor Karaszi on 14 Apr 2010 06:23
OBJECTPROPERTY is resolved in current database. It is likely to be resolved to NULL causing the IF to be FALSE. I would skip that part. Alternatively, you can do explicit SELECTs against the catalog views inside that database. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "GW" <GW(a)discussions.microsoft.com> wrote in message news:7DD62CBF-86E4-4AAA-AE57-222CF775A1DB(a)microsoft.com... > hi, > > need help, > > I have two databases. I have lines in SP running in Database A of which > will > check table existence in Database B but it did not find any table even the > said table exists in Database B. Is this possible or did I miss something? > > The following failed:- > > if exists ( select * from dbo.sysobjects where id = > object_id(N'[DATABASE_B].[dbo].[TABLE_A]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > > drop table [DATABASE_B].[dbo].[TABLE_A] > > TQVM. |