Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question on OBJECTPROPERTY Expand / Collapse
Author
Message
Posted Tuesday, July 13, 2010 9:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
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.
Post #951590
Posted Tuesday, July 13, 2010 9:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #951623
Posted Tuesday, July 13, 2010 10:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
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)
Post #951629
Posted Tuesday, July 13, 2010 10:07 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #951633
Posted Tuesday, July 13, 2010 10:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #951638
Posted Tuesday, July 13, 2010 10:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
I will do so... Thank you a lot for helping me with this one!!!
Post #951639
Posted Tuesday, July 13, 2010 10:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #951646
Posted Tuesday, July 13, 2010 11:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
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
Post #951675
Posted Tuesday, July 13, 2010 11:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 11:25 AM
Points: 348, Visits: 710
SP with an OUTPUT parameter... not perfect but good enough
Post #951694
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse