sys.identity_columns

This is the third(see edit below) catalog view that I will cover, I have already covered sys.dm_exec_sessions and sys.dm_db_index_usage_stats For a list of all the catalog views click here

EDIT: Obviously I need to get more sleep because sys.identity_columns is not one of the Dynamic Management Views

Today we will talk about the sys.identity_columns Object Catalog View
The view sys.identity_columns contains a row for each column that is an identity column
If you look at this view in Books On Line you will notice that there are only 4 columns described
seed_value
increment_value
last_value
is_not_for_replication

However when you run SELECT * FROM sys.identity_columns you get back 26 columns. My first impression was that the documentation was incomplete, however when you look closer you will see that the first thing mentioned is
[columns inherited from sys.columns] For a list of columns that this view inherits, see sys.columns (Transact-SQL).
So this view return 4 columns in addition to what the sys.columns view returns

Make sure that you are using SQL Server 2005 and are in the AdventureWorks database
Okay so let's start, the way I write about these views is that I don't want to just copy what is in BOL. I try to have a couple of queries that will show you how to accomplish some things by using these views

Let's select all columns that have the identity property set by using the sys.columns view

SELECT *
FROM sys.columns
WHERE is_identity =1

Now run the following query

SELECT *
FROM sys.identity_columns

As you can see you get back the same number of rows only you get 4 additional columns back which have some information that only deal with identity columns


EDIT: Someone left me a comment (thank you) and I decided to update the post, the following query

SELECT * FROM sys.columns
WHERE is_identity =1

will return a column names is_computed, but the following query below will not

SELECT *
FROM sys.identity_columns

Which makes sense since an identity column can not be computed
END EDIT

Let's try some other things, let's select only the columns that have a tinyint as an identity column, this is easy to do we just join with the sys.types view


SELECT i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE t.name ='tinyint'

Or let's see how many different integer data types have the identity property set

SELECT t.name,COUNT(*) as GroupedCount
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
GROUP BY t.name

And the results are below

name GroupedCount
----------------------
bigint 3
int 43
smallint 3
tinyint 2

So that is all fine but how do we know what table this column belongs to? That's pretty easy to do we can just use the OBJECT_NAME function
--tinyint only
SELECT OBJECT_NAME(object_id) AS TableName,t.name,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE t.name ='tinyint'


--smallint only
SELECT OBJECT_NAME(object_id) AS TableName,t.name,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE t.name ='smallint'




There is a small problem with using OBJECT_NAME(object_id) to get the table name
We should be using this instead

SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) as FullTableName

Why am I doing this? This is because SQL server 2005 introduced schemas
so basically if we do this

SELECT * FROM HumanResources.Department

we have no problem, but if we run the following query

SELECT * FROM Department

we get this friendly message

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Department'.

Now let's create a Department table

CREATE TABLE Department (ID INT IDENTITY NOT NULL)

When we run this query

SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) AS FullTableName,
CASE t.name WHEN 'tinyint' THEN 255 -COALESCE(CONVERT(INT,last_value),0)
WHEN 'smallint' THEN 32767 -COALESCE(CONVERT(INT,last_value),0)
WHEN 'int' THEN 2147483647 -COALESCE(CONVERT(INT,last_value),0)
WHEN 'bigint' THEN 9223372036854775807 -COALESCE(CONVERT(INT,last_value),0)
END AS ValuesLeft ,
OBJECT_NAME(object_id) as TableName,t.name,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE seed_Value > 0
AND OBJECT_NAME(object_id) like 'DE%'
ORDER BY TableName

We will see two rows

dbo.Department 2147483647 Department int
HumanResources.Department 32751 Department smallint

One table is dbo.Department and the other table is HumanResources.Department

Now when we run the same queries again there is no problem
SELECT * FROM Department


SELECT * FROM HumanResources.Department

You can read up more on schemas in BOL, let's get back to sys.identity_columns


let's do something interesting now, let's find out how many more identity values we have left until we run out

SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) AS FullTableName,
CASE t.name WHEN 'tinyint' THEN 255 -COALESCE(CONVERT(INT,last_value),0)
WHEN 'smallint' THEN 32767 -COALESCE(CONVERT(INT,last_value),0)
WHEN 'int' THEN 2147483647 -COALESCE(CONVERT(INT,last_value),0)
WHEN 'bigint' THEN 9223372036854775807 -COALESCE(CONVERT(INT,last_value),0) END AS ValuesLeft ,
OBJECT_NAME(object_id) as TableName,t.name,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE seed_Value > 0
ORDER BY FullTableName


You can also calculate the percentage used

SELECT SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) AS FullTableName,
CASE t.name WHEN 'tinyint' THEN 100 -(255 -COALESCE(CONVERT(INT,last_value),0)) /255.0 * 100
WHEN 'smallint' THEN 100 -( 32767 -COALESCE(CONVERT(INT,last_value),0)) /32767.0 * 100
WHEN 'int' THEN 100 -(2147483647 -COALESCE(CONVERT(INT,last_value),0)) /2147483647.0 * 100
WHEN 'bigint' THEN 100 -(9223372036854775807 -COALESCE(CONVERT(INT,last_value),0)) /9223372036854775807.0 * 100
END AS PercentageUsed ,
OBJECT_NAME(object_id) as TableName,t.name,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE seed_Value > 0
ORDER BY PercentageUsed DESC


Now let's find all the columns where the seed value is not 1
SELECT seed_value,SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) AS FullTableName,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE seed_value > 1
ORDER BY FullTableName


Now let's find all the columns where the increment value is not 1
SELECT increment_value,SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) AS FullTableName,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE increment_value <> 1
ORDER BY FullTableName

No rows are returned, now let's put that to the test by creating a table and have the value increment by 5

CREATE TABLE dbo.Department2 (ID INT IDENTITY (1,5) NOT NULL)

Now run the query again

SELECT increment_value,SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) AS FullTableName,i.*
FROM sys.identity_columns i JOIN sys.types t ON i.user_type_id = t.user_type_id
WHERE increment_value <> 1
ORDER BY FullTableName

And that covers some uses of the sys.identity_columns view, the only thing left is the description of the view itself


Column name
Data type Description

object_id
int ID of the object to which this column belongs.

name
sysname Name of the column. Is unique within the object.

column_id
int ID of the column. Is unique within the object.
Column IDs might not be sequential.

system_type_id
tinyint ID of the system type of the column.

user_type_id
int ID of the type of the column as defined by the user.
To return the name of the type, join to the sys.types catalog view on this column.

max_length
smallint Maximum length (in bytes) of the column.
-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'.

precision
tinyint Precision of the column if numeric-based; otherwise, 0.

scale
tinyint Scale of column if numeric-based; otherwise, 0.

collation_name
sysname Name of the collation of the column if character-based; otherwise, NULL.

is_nullable
bit 1 = Column is nullable.

is_ansi_padded
bit 1 = Column uses ANSI_PADDING ON behavior if character, binary, or variant.
0 = Column is not character, binary, or variant.

is_rowguidcol
bit 1 = Column is a declared ROWGUIDCOL.

is_identity
bit 1 = Column has identity values

is_filestream
bit Reserved for future use.

is_replicated
bit 1 = Column is replicated.

is_non_sql_subscribed
bit 1 = Column has a non-SQL Server subscriber.

is_merge_published
bit 1 = Column is merge-published.

is_dts_replicated
bit 1 = Column is replicated by using SQL Server 2005 Integration Services (SSIS).

is_xml_document
bit 1 = Content is a complete XML document.
0 = Content is a document fragment or the column data type is not xml.

xml_collection_id
int Nonzero if the data type of the column is xml and the XML is typed. The value will be the ID of the collection containing the validating XML schema namespace of the column.
0 = No XML schema collection.

default_object_id
int ID of the default object, regardless of whether it is a stand-alone object sys.sp_bindefault, or an inline, column-level DEFAULT constraint. The parent_object_id column of an inline column-level default object is a reference back to the table itself.
0 = No default.

rule_object_id
int ID of the stand-alone rule bound to the column by using sys.sp_bindrule.
0 = No stand-alone rule. For column-level CHECK constraints, see sys.check_constraints (Transact-SQL).


seed_value
sql_variant Seed value for this identity column. The data type of the seed value is the same as the data type of the column itself.

increment_value
sql_variant Increment value for this identity column. The data type of the seed value is the same as the data type of the column itself.

last_value
sql_variant Last value generated for this identity column. The data type of the seed value is the same as the data type of the column itself.

is_not_for_replication
bit Identity column is declared NOT FOR REPLICATION.