|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
|
|
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.
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660,
Visits: 29,913
|
|
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
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
|
|
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)
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
|
|
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
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
|
|
| I will do so... Thank you a lot for helping me with this one!!!
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660,
Visits: 29,913
|
|
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
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
|
|
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
|
|
|
|
|
Old Hand
      
Group: General Forum Members
Last Login: Thursday, March 14, 2013 1:34 PM
Points: 348,
Visits: 703
|
|
| SP with an OUTPUT parameter... not perfect but good enough
|
|
|
|