How to get Identity Columns details

Many times developers need to know identity columns related information from tables.
After SQL Server 2005 , its easy for us to get this info with much rich informations.



sys.identity_columns is a table which have all this information we need.

CREATE TABLE testtable (
id INT IDENTITY ( 1 , 1 ))

INSERT INTO testtable
DEFAULT VALUES

INSERT INTO testtable
DEFAULT VALUES

INSERT INTO testtable
DEFAULT VALUES

When we look at last value it will show last added value for identity column.
This table will provide basic info like table name, column name, data type,seed , increment value etc.

SELECT Object_name(object_id) table_name,

object_id,

name,

column_id,

seed_value,

increment_value,

last_value,

system_type_id,

(SELECT name

FROM sys.types

WHERE system_type_id = i.system_type_id) AS system_type_name,

user_type_id,

(SELECT name

FROM sys.types

WHERE user_type_id = i.user_type_id) AS user_type_name,

max_length,

PRECISION,

scale,

collation_name,

is_nullable,

is_ansi_padded,

is_rowguidcol,

is_identity,

is_filestream,

is_replicated,

is_non_sql_subscribed,

is_merge_published,

is_dts_replicated,

is_xml_document,

xml_collection_id,

default_object_id,

rule_object_id,

is_not_for_replication,

is_computed,

is_sparse,

is_column_set

FROM sys.identity_columns i

WHERE Objectproperty(object_id,'isusertable') = 1