August 18, 2004 at 8:47 pm
Which system table maintenance the information of all identity columns?
For example:
If you create a table test:
CREATE TABLE test(id INT IDENTITY(10,2))
Not allow use the funcions IDENT_SEED and IDENT_INCR, I want to get the increment value(2) and the seed value (10) of an identity column (id) by query system table.
Thanks
August 19, 2004 at 6:41 am
The name of the table is dbo.ALLOCATION with an object id of 99. You won't find it in sysobjects but if you run SELECT OBJECT_NAME(99) you will see it really exists. This however is a protected system table and is not made access under any circumstance to you as far as I can find. It handles pretty much all ALLOCATION items such as Object ID, Identity Columns advancement, page allocation and some other items I haven't quite firgured out yet.
August 20, 2004 at 2:46 pm
August 20, 2004 at 3:31 pm
The following query will return all the table names and column names with the seed and increment values.
SELECT left (so.name, 30),
left (sc.name, 40),
cast (substring(sc.autoval, 5, 1) as integer),
cast (substring(sc.autoval, 9, 1) as integer)
FROM syscolumns sc
INNER JOIN
sysobjects so
ON sc.id = so.id
WHERE cast (substring(sc.autoval, 5, 1) as integer) >= 1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply