Search This Blog

Sunday, October 19, 2008

Current Identity value, Identity usage and other identity information

SELECT
QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME,
C.NAME AS COLUMNNAME,
CASE C.SYSTEM_TYPE_ID WHEN 127 THEN 'BIGINT' WHEN 56 THEN 'INT'
WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE,
IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT,
IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED,
CASE C.SYSTEM_TYPE_ID
WHEN 127 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 9223372036854775807
WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647
WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767
WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1
ORDER BY PERCENTAGEUSED DESC

No comments: