Querying SQL Server / Oracle Database Table Data - Identity Column

Question

I am trying to query a SQL Server & Oracle database to get table data that would indicate if a column is auto increment or or not. I would assume you would look at the "information_schema.columns" and "user_tab_cols" tables but it doesn't seem to hold this information. Does anyone know where I can retrieve this information?

Answer 1

One way in sql server, this will return all the columns that have identity

    select OBJECT_NAME(id) as Tablename, name as ColumnName,*
    from syscolumns
   where COLUMNPROPERTY(id, name, 'IsIdentity') = 1

Or by using information_schema

select TABLE_SCHEMA + '.' + TABLE_NAME,COLUMN_NAME
 from INFORMATION_SCHEMA.columns
where COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), 
   COLUMN_NAME, 'IsIdentity') = 1

Answer 2

To check a specific column in SQL Server:

select t.name as tableName, c.name as columnName, c.is_identity 
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
    where t.name = 'YourTable'
        and c.name = 'YourColumn'

or to find all identity columns

select t.name as tableName, c.name as columnName, c.is_identity 
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
    where c.is_identity = 1