How To: Check whether all columns with same name in a database are identity columns

DECLARE @identity_col_name nvarchar(20)
DECLARE @tablename nvarchar(50)
DECLARE @colname nvarchar(50)
DECLARE @strSQL nvarchar(4000)
DECLARE @flag int

--SET identity column name
SET @identity_col_name = 'DEX_ROW_ID'

CREATE TABLE #temp_identities
(
TableName nvarchar(50),
ColumnName nvarchar(50),
IdentityValue int,
Flag int
)

DECLARE ident_cursor CURSOR
FOR
select t.name as TableName,c.name as ColName, 'Flag'=case COLUMNPROPERTY
(c.id,c.name,'IsIdNotForRepl')
when 1 then 2
else 1
end
from sysobjects t join
syscolumns c on t.id=c.id where t.xtype='U' and c.name=@identity_col_name
and COLUMNPROPERTY (c.id,c.name,'isidentity')=1
order by TableName

OPEN ident_cursor
FETCH NEXT FROM ident_cursor
INTO @tablename, @colname, @flag
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = N'INSERT INTO #temp_identities VALUES(''' + @tablename +
''',''' + @colname + ''',' + 'IDENT_CURRENT(''' + @tablename + '''),' +
cast(@flag as varchar(10)) + ')'
exec sp_executeSQL @strSQL
FETCH NEXT FROM ident_cursor INTO @tablename, @colname,@flag
END

CLOSE ident_cursor
DEALLOCATE ident_cursor

INSERT INTO #temp_identities select t.name as TableName,c.name as
ColumnName, 0 as IdentityValue, 0 as Flag from sysobjects t join
syscolumns c on t.id=c.id where t.xtype='U' and c.name=@identity_col_name
and COLUMNPROPERTY (c.id,c.name,'isidentity')=0
order by TableName

/* Output the temp result */
--0 = No
--1 = Yes
--2 = Yes (Not for Replication)
SELECT * FROM #temp_identities order by TableName
DROP TABLE #temp_identities