Question on OBJECTPROPERTY

Question

Question on OBJECTPROPERTY

virgilrucsandescu

SSCrazy

Points: 2457
More actions
July 13, 2010 at 9:32 am

#224841

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 SSCrazy Points: 2457 More actions · Answer 2

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 SSC Guru Points: 150467 More actions · Answer 3

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
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

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
SQLkiwi blog
@SQL_Kiwi

virgilrucsandescu SSCrazy Points: 2457 More actions · Answer 5

I will do so... Thank you a lot for helping me with this one!!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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 SSCrazy Points: 2457 More actions · Answer 7

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 SSCrazy Points: 2457 More actions · Answer 8

SP with an OUTPUT parameter... not perfect but good enough

David Herpin Grasshopper Points: 17 More actions · Answer 9

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.