Sql Server : Script to retrieve all IDENTITY columns with SEED & Increment details

Quick one today:

Occasionally, we find a need to retrieve all the IDENTITY columns in all our environments to perform analysis on some architectural tickets. Since there could be many databases across different environments, we need a single script that could retrieve necessary data — so we could point to to a multi-script and BAM !! All data retrieved in one shot !!

--
--  Retrieve IDENTITY columns for all tables in a database
--
SELECT	  	  @@SERVERNAME				AS [ServerName]
		, DB_NAME()				AS [DatabaseName]
		, O.name				AS [TableName]
		, C.name				AS [ColumnName]
		, T.name 				AS [DataType]
		, I.seed_value
		, I.increment_value
		, I.last_value
		, IDENT_CURRENT(O.name) AS [IdentCurrent]
		, I.is_not_for_replication
		, I.is_nullable
		, I.column_id
		, I.user_type_id
FROM sys.objects AS O
INNER JOIN sys.columns AS C
	ON O.object_id = C.object_id
	AND O.is_ms_shipped = 0
INNER JOIN sys.identity_columns AS I
	ON C.name = I.name
	AND C.object_id = I.object_id
	AND I.is_identity = 1
INNER JOIN sys.types AS T
	ON T.user_type_id = I.user_type_id
ORDER BY @@SERVERNAME, DB_NAME(), O.name, C.name
GO

 

Result

Output will look something like this:

Hope this helps,
_Sqltimes

 

Read Full Post »