What are all the tables with an identity column?

This is specific to Microsoft SQL Server 2005

  • An identity column is a feature that allows MS SQL Server to automatically number the next row.
  • The next row will be: max(column) + 1, or whatever increment was defined.
  • The number is always unique.
  • You should always create a unique index on that column, this will make it instantaneous. Otherwise it will be extremely slow on large tables. MS SQL Server will create a temporary index on the fly.
  • NEVER use the identity column as a consecutive number such as invoices... There is no guarantee that these number will be consecutive.

Summary

How to get the list of all tables with identity columns with MS SQL server 2005?

  • You cannot view all the tables when using Visual Studio, you must do it one table at a time.

Identity columns

use AdventureWorksLT;
go

SELECT SCHEMA_NAME(schema_id) AS "Schema Name",
tbl.name AS "Table Name",
col.name AS "Column Name"
FROM sys.tables as tbl JOIN sys.identity_columns as col ON tbl.object_id = col.object_id
ORDER BY 1,2

Schema Name    Table Name           Column Name
-------------- -------------------- --------------------
dbo            BuildVersion         SystemInformationID
dbo            ErrorLog             ErrorLogID
SalesLT        Address              AddressID
SalesLT        Customer             CustomerID
SalesLT        Product              ProductID
SalesLT        ProductCategory      ProductCategoryID
SalesLT        ProductDescription   ProductDescriptionID
SalesLT        ProductModel         ProductModelID
SalesLT        SalesOrderDetail     SalesOrderDetailID
SalesLT        SalesOrderHeader     SalesOrderID

(10 row(s) affected)
  • Do not forget to first select the database that you want to use.
  • This will give you all the tables and their identity columns for the database selected.
  • This includes all the schemas.