February 2, 2012 at 2:02 pm
I am using SQL Server 2008 R2. I created a type such as
create type myTest as table (myCnt int identity(2,1))
But the IDENT_SEED() and IDENT_INCR() return null for my table type. Since these convenience functions do not work for user table types, is there a system view where I can look up these values?
February 2, 2012 at 3:11 pm
I suspect you'd have to declare a table of that type to check the identity properties. The type doesn't have an identity itself, it's just the definition for a table
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
February 2, 2012 at 4:30 pm
Look up this function : IDENT_CURRENT
February 6, 2012 at 11:07 am
I resolved this issue. I can get the seed and increment values from the sys.identity_columns with the select:
select seed_value, increment_value
from sys.identity_columns
where object_name(object_id) like 'TT_' + <table type name> + '%'
April 11, 2014 at 3:51 am
Thanks, your reply helped. To expand on what you have, to take schema/owner into account and to use object_id instead of name, this is what I used
SELECT o.object_id, s.name AS schemaOwner, t.name AS tableType, ic.seed_value, ic.increment_value
FROM sys.table_types t
JOIN sys.objects o on o.object_id = t.type_table_object_id
JOIN sys.schemas s on s.schema_id = t.schema_id
LEFT JOIN sys.identity_columns ic ON ic.object_id = o.object_id
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply