각 테이블의 Identity 사용현황 조사쿼리

1. -- MSSQL 2005
2. SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id))+'.'+  QUOTENAME(t.name)AS TableName
3.        ,c.name AS ColumnName
4.        ,CASE c.system_type_id
5.         WHEN 127 THEN 'bigint'
6.         WHEN 56  THEN 'int'
7.         WHEN 52  THEN 'smallint'
8.         WHEN 48  THEN 'tinyint'
9.         END AS'DataType'
10.        ,IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  +'.'+ t.name)AS CurrentIdentityValue
11.        ,CASE c.system_type_id WHEN 127 THEN 9223372036854775807
12.         WHEN 56  THEN 2147483647
13.         WHEN 52  THEN 32767
14.         WHEN 48  THEN 255 END - IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  +'.'+ t.name)AS REMIANIDENTITYVALUE
15.        ,CASE c.system_type_id WHEN 127 THEN 9223372036854775807
16.         WHEN 56  THEN 2147483647
17.         WHEN 52  THEN 32767
18.         WHEN 48  THEN 255 END AS MAXIDENTITYVALUE
19.        ,CASE c.system_type_id
20.         WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  +'.'+ t.name)* 100.)/9223372036854775807
21.         WHEN 56  THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  +'.'+ t.name)* 100.)/2147483647
22.         WHEN 52  THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  +'.'+ t.name)* 100.)/32767
23.         WHEN 48  THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  +'.'+ t.name)* 100.)/ 255
24.         END AS'PercentageUsed'  
25. FROM sys.COLUMNSAS c INNERJOIN sys.TABLESAS t ON t.[object_id]= c.[object_id]
26. WHERE c.is_identity = 1
27. ORDERBY TABLENAME
28.  
29. -- MSSQL 2000
30. SELECT o.name AS TableName
31.       ,c.name AS ColumnName
32.       ,CASE c.type
33.        WHEN 127 THEN 'bigint'
34.        WHEN 56  THEN 'int'
35.        WHEN 52  THEN 'smallint'
36.        WHEN 48  THEN 'tinyint'
37.        END AS'DataType'
38.       ,IDENT_CURRENT(o.name)AS CurrentIdentityValue
39.       ,CASE c.type WHEN 127 THEN 9223372036854775807
40.        WHEN 56 THEN 2147483647
41.        WHEN 52 THEN 32767
42.        WHEN 48 THEN 255 END - IDENT_CURRENT(o.name)AS REMIANIDENTITYVALUE
43.       ,CASE c.type WHEN 127 THEN 9223372036854775807
44.        WHEN 56 THEN 2147483647
45.        WHEN 52 THEN 32767
46.        WHEN 48 THEN 255 END AS MAXIDENTITYVALUE
47.       ,CASE c.type
48.        WHEN 127 THEN (IDENT_CURRENT(o.name)* 100.)/ 9223372036854775807
49.        WHEN 56  THEN (IDENT_CURRENT(o.name)* 100.)/ 2147483647
50.        WHEN 52  THEN (IDENT_CURRENT(o.name)* 100.)/ 32767
51.        WHEN 48  THEN (IDENT_CURRENT(o.name)* 100.)/ 255
52.        END AS'PercentageUsed'  
53. FROM   syscolumns c WITH(nolock)
54.      , sysobjects o WITH(nolock)
55. WHERE  c.id = o.id
56. AND    o.xtype ='U'
57. AND    COLUMNPROPERTY(o.id, c.name,'IsIdentity')= 1
58. ORDERBY o.name