Question I received in an Email: How to list all the columns in the database which are used as identity key in my database?
Answer: Run following query in query editor.
USE AdventureWorks GO SELECT SCHEMA_NAME(schema_id) AS schema_name, t.name AS table_name, c.name AS column_name FROM sys.tables AS t JOIN sys.identity_columns c ON t.OBJECT_ID = c.OBJECT_ID ORDER BY schema_name, table_name; GO
Here are few additional blog posts which discuss about identity
- SQL SERVER – Jump in Identity Column After Restart
- SQL SERVER – DELETE, TRUNCATE and RESEED Identity
- SQL SERVER – Answer – Value of Identity Column after TRUNCATE command
- SQL SERVER – Finding Out Identity Column Without Using Column Name
- SQL SERVER – Enable Identity Insert – Import Expert Wizard
- SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record
- SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table
Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table.
Answer: There are two answers – No and Yes. Let us see them one by one.
Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server. You will just have to add a new column.
Answer Yes – If you want to add new column to the table, it is totally possible to do so with the help of following a script.
1 | ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1) |
Reference : Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
This will provide you with the columns that have Identity insert on, but not the Key columns
Another method that works in all versions is
select table_schema, table_name,column_name from information_schema.columns
where columnproperty(object_id(table_name),column_name,’IsIdentity’)=1
order by table_schema, table_name