Where are seed_value and increment_value for IDENTITY columns?

Question

I'm collecting metadata using the sys.* views, and according to the documentation, the sys.identity_columns view will return the seed and increment values like so.

CREATE TABLE ident_test (
  test_id int IDENTITY(1000,10),
  other int
)

SELECT name, seed_value, increment_value
  FROM sys.identity_columns
 WHERE object_id = OBJECT_ID( 'ident_test' )

However, the above query just returns one column. Is it just me?

(Note: I've had to change this question somewhat from its earlier version.)

Answer 1

Are you sure you are running this in a database with tables with IDENTITY columns?

SELECT c.name, i.seed_value, i.increment_value
FROM sys.columns c
INNER JOIN sys.identity_columns i
    ON i.object_id = c.object_id
    AND i.column_id = c.column_id

Returns rows for me in a regular production database with a few identities.

Using a LEFT JOIN returns these rows as well as many which are not IDENTITY

I ran this on another database, and I noticed some NULLs are returned (even in the INNER JOIN case). This is because some of the columns are in VIEWs.

Try adding:

INNER JOIN sys.tables t
ON t.object_id = c.object_id

To filter only to actual IDENTITY columns in tables.

Answer 2

Shouldn't you reverse the from and join, like this:

SELECT c.name, i.seed_value, i.increment_value
from sys.identity_columns i
join sys.columns c
    ON i.object_id = c.object_id
   AND i.column_id = c.column_id

Answer 3

You are missing the Where clause. Your query is effectively saying 'Give me all of sys.columns and any matching rows from sys.identity_columns you have (but give me null if there are no matching rows)'.

By adding the Where clause below you'll change it to only return where an exact match is returned, which is the same as an inner join in this instance really.

SELECT c.name, i.seed_value, i.increment_value FROM sys.columns c LEFT OUTER JOIN sys.identity_columns i ON i.object_id = c.object_id AND i.column_id = c.column_id Where I.seed_value is not null

So I think your data is correct, there are no results to view though.

Answer 4

your query returns what I'd expect [see below]; it returns the single meta-data row about the single identity column (test_ID) in table (ident_test), the oter column (other) has no meta-data in the sys.identity_column as is is not an identity.

SELECT name, seed_value, increment_value
  FROM sys.identity_columns
 WHERE object_id = OBJECT_ID( 'ident_test' )

select name, is_identity, is_nullable
from sys.columns
WHERE object_id = OBJECT_ID( 'ident_test' )

Which gives

name        seed_value    increment_value
-----------------------------------------
test_id     1000          10
(1 row(s) affected)

name        is_identity   is_nullable
-------------------------------------
test_id     1           0
other       0           1

(2 row(s) affected)