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
精彩评论