开发者

Querying SQL Server / Oracle Database Table Data - Identity Column

开发者 https://www.devze.com 2023-01-11 19:51 出处:网络
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

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 an开发者_如何学Cyone know where I can retrieve this information?


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


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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号