SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question on OBJECTPROPERTY


Question on OBJECTPROPERTY

Author
Message
virgilrucsandescu
virgilrucsandescu
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 712
This calls return NULL even if "TableName" exists in the Development DB and definitely has an identity field.

1a. SELECT OBJECTPROPERTY(OBJECT_ID('Development..TableName'), 'TableHasIdentity')
1b. SELECT OBJECTPROPERTY(OBJECT_ID('Development.dbo.TableName'), 'TableHasIdentity')


If I run it in "Development" DB then it returns the correct value :1
2.a
USE Development
GO
SELECT OBJECTPROPERTY(OBJECT_ID(TableName'), 'TableHasIdentity')

My problem is that I have to write a SP that has the DB name as a parameter and then copy some data from a DB into another one, so I need to run it like in 1a example (but I tried the same queries in SSMS and it is the same..)

Very weird is that for other tables that have identity fields 1a (or 1b) returns correctly "1" ...
More weird that :

SELECT OBJECTPROPERTY(OBJECT_ID('Development..TableName'), 'IsTable') returns NULL (but again, only for this table)

but of course SELECT * FROM Development..TableName works fine...

Any help will be really appreciated.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228964 Visits: 46344
ObjectProperty only works in the context of the current database. It has no parameters to know to look in other databases, just an objectID as the first parameter.

From Books Online:
id
Is an expression that represents the ID of the object in the current database. id is int and is assumed to be a schema-scoped object in the current database context.



Edit: Corrected.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


virgilrucsandescu
virgilrucsandescu
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 712
Weird that it works fine for the other tables in Development DB (running it from a different context)..
It must be something undocumented ...

Then I have a second question - how could I check in code if a table (from a different DB) has an identity field?
If OBJECTPROPERTY works only with local objects it doesn't make sense to make it dynamic and considering that USE WhateverDB doesnt work in SP, is there any way for me to check if a table in a different DB has an identity field?

(I need this to SET IDENTITY_INSERT ON whenever a table has an identity field)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36168 Visits: 11361
virgilrucsandescu (7/13/2010)
Weird that it works fine for the other tables in Development DB .. It must be something undocumented ...

Possibly the object id returned by OBJECT_ID happens to match a table with an identity column in the context database - you could easily run a query to verify that.

As far as the requirement is concerned, try checking Development.sys.identity_columns instead.

BTW OBJECT_ID does have a second parameter - the object type. As a good practice, I encourage people to specify it. If you are looking for a user table, the second parameter would be 'U'. The types are listed in Books Online under the entry for sys.objects.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36168 Visits: 11361
An example (based on a test database of mine):


-- Just to make the point
USE master;
GO
IF EXISTS
(
SELECT *
FROM Sandpit.sys.identity_columns
WHERE object_id = OBJECT_ID(N'Sandpit.dbo.Product', N'U')
)
BEGIN
PRINT 'It does have an identity column';
END
ELSE
BEGIN
PRINT 'Nope';
END;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
virgilrucsandescu
virgilrucsandescu
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 712
I will do so... Thank you a lot for helping me with this one!!!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228964 Visits: 46344
Paul White NZ (7/13/2010)
virgilrucsandescu (7/13/2010)
Weird that it works fine for the other tables in Development DB .. It must be something undocumented ...

Possibly the object id returned by OBJECT_ID happens to match a table with an identity column in the context database


Very likely. Object ids are only unique in the context of a database, they are not unique across the entire instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


virgilrucsandescu
virgilrucsandescu
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 712
Is there any function that could be used for this (check if a table in a different context has any identity field)?

I have to check this in 1 million places, I hate to copy-paste the code ... (and of course an UDF is not possible given the sp_executesql...)

set @sql = N' SELECT @cnt = COUNT(*) FROM @DBName.sys.identity_columns WHERE object_id = OBJECT_ID(N'' @prmDB.dbo.@prmTable'', N''U'')'

SELECT @params = N'@DBName varchar, @cnt int OUTPUT'
EXEC sp_executesql @sql, @params, @prmDB, @cnt = @table_has_identity OUTPUT

IF @table_has_identity = 0
virgilrucsandescu
virgilrucsandescu
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 712
SP with an OUTPUT parameter... not perfect but good enough
David Herpin
David Herpin
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 49
You can create a wrapper function in the non-current database like so:

create function ObjectProperty(@objId int, @property varchar(20)) returns int as begin
--Because ObjectProperty runs in the context of the current DB, we need a wrapper function in the DB we want it to run in the context of.
return objectproperty(@objId, @property)
end


Then, instead of calling ObjectProperty(...), call dbname.dbo.ObjectProperty(...).

This should work.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search