What is SPID in SQL Server

Here is a script which list out all the identity columns and percentage used

 

SET NOCOUNT ON
SELECT	SCHEMA_NAME(t.schema_id) + '.' +  t.name AS TableName, 
idc.name AS ColumnName,
CASE idc.system_type_id
	WHEN 127 THEN 'bigint'
	WHEN 56 THEN 'int'
	WHEN 52 THEN 'smallint'
	WHEN 48 THEN 'tinyint'
END AS 'DataType',
idc.last_value AS CurrentIdentityValue,idc.seed_value,increment_value,
CASE idc.system_type_id
	WHEN 127 THEN ((CONVERT(INT, idc.last_value) - CONVERT(INT, idc.seed_value) )+1 )* 100. / 9223372036854775806/CONVERT(INT,increment_value)
	WHEN 56 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 )* 100.  / 2147483646/CONVERT(INT,increment_value)
	WHEN 52 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 )* 100.  / 32766/CONVERT(INT,increment_value)
	WHEN 48 THEN ((CONVERT(INT, idc.last_value)- CONVERT(INT, idc.seed_value) )+1 ) * 100. / 256/CONVERT(INT,increment_value)
END AS 'PercentageUsed' 
FROM	sys.tables AS t
INNER JOIN sys.identity_columns idc ON idc.object_id = t.object_id
WHERE	idc.is_identity = 1
ORDER BY PercentageUsed DESC