引用來源
---
這篇真是寶貴呀,太棒了!!
SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS TableName,
c.COLUMN_NAME AS ColumnName,
c.DATA_TYPE AS 'DataType',
IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
CASE c.DATA_TYPE
WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 2147483647
WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 32767
WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 255
END AS 'PercentageUsed'
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN
INFORMATION_SCHEMA.TABLES AS t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1
AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint')
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY PercentageUsed DESC
沒有留言:
張貼留言