SQL Server Portal

Column identity is something that we come across every now and then. We use it to generate an auto number column in any table.
Today, we will discuss multiple ways to find identity column in the entire user tables.

Method 1 : (sys.columns)

Use Adventureworks
GO

Select Object_Name([object_id]) as [Table Name]
,[name] as [Column Name]
,is_identity
from sys.columns
Where is_identity=1 And Objectproperty(object_id,'IsUserTable')=1

Method 2 : (sys.objects & sys.all_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.objects A
Inner Join sys.all_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 3 : (sys.tables & sys.all_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.tables A
Inner Join sys.all_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 4 : (sys.objects & sys.identity_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.objects A
Inner Join sys.identity_columns B
On A.[object_id]=B.[object_id]
Where A.type='U'

Method 5 : (sys.tables & sys.identity_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.tables A
Inner Join sys.identity_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 6 : (INFORMATION_SCHEMA.COLUMNS)

Use Adventureworks
GO

;With CTE AS (Select Table_Schema+'.'+Table_Name as [Table_Name],[Column_name] from
INFORMATION_SCHEMA.COLUMNS)
Select Table_Name
,[Column_name]
,COLUMNPROPERTY(OBJECT_ID(Table_Name),[Column_name],'IsIdentity')AS 'IsIdentity'
from CTE Where
COLUMNPROPERTY( OBJECT_ID(Table_Name),[Column_name],'IsIdentity')=1

How SQL Server checks the identity of the table

I opened a table having identity column in the design mode and at the same time opened SQL server profile to view the qurey. Given below is the query.

In this query SQL picks a lot of information but what we need to check is identity column and it is available on line # 4.

select col.name, col.column_id, st.name as DT_name, schema_name(st.schema_id) as DT_schema
, col.max_length, col.precision, col.scale, bt.name as BT_name, col.collation_name
, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol,
col.is_identity
, case when(idc.column_id is null) then null else CONVERT(nvarchar(40)
, idc.seed_value) end, case when(idc.column_id is null) then null else CONVERT(nvarchar(40)
, idc.increment_value) end
, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as IsIdNotForRepl
, col.is_replicated, col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name
, schema_name(robj.schema_id) as Rul_schema, col.default_object_id
, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst
, dobj.name as def_name, schema_name(dobj.schema_id) as def_schema
, CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_FullTextCol
, col_name(col.object_id, ftc.type_column_id) FT_type_column, ftc.language_id as FT_language_id
, case when(cmc.column_id is null) then null else cmc.definition end as formular
, case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted
, defCst.definition, COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic
, xmlcoll.name as xmlSchema_name, schema_name(xmlcoll.schema_id) as xmlSchema_schema
, col.is_xml_document, col.is_sparse, col.is_column_set
, CONVERT(bit, case when (ISNULL(ftc.statistical_semantics, 0) = 0) then 0 else 1 end)
as is_StatisticalSemantics, col.is_filestream
from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id
left outer join sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R'
left outer join sys.objects dobj on dobj.object_id = col.default_object_id
and dobj.type = 'D'
left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id
left outer join sys.identity_columns idc on idc.object_id = col.object_id
and idc.column_id = col.column_id
left outer join sys.computed_columns cmc on cmc.object_id = col.object_id
and cmc.column_id = col.column_id
left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id
and ftc.column_id = col.column_id
left outer join sys.xml_schema_collections xmlcoll
on xmlcoll.xml_Collection_id = col.xml_Collection_id
where col.object_id =
object_id(N'dbo.tbl_test')
order by col.column_id

Read Full Post »