Search code examples
sql-server-2005metadatasystem-views

Where are seed_value and increment_value for IDENTITY columns?

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.)

Solution

  • 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