怎樣在SQL Server中檢查 IDENTITY 列

在SQL Server資料庫中我們建表的時候經常用到 IDENTITY 屬性來定義列的自增長, 但是怎樣才能快速有效的去檢查這個列的配置正確以及使用的百分比呢. 找了很多資料,覺得下面的語句是最方便的.

 通過建立下面的儲存過程，呼叫就OK了。

EXEC dbo.CheckIdentities
GO

/* The SQL Server 2005 version of the stored procedure. It uses new catalog views */


CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON

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,
CASE c.system_type_id
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC
END

如果在SQL Server 2000 中呼叫這個儲存過程那麼會出現下面的錯誤：

Server: Msg 195, Level 15, State 10, Procedure a, Line 4
'SCHEMA_NAME' is not a recognized function name.

在SQL Server 2000 中這個儲存過程應該變更為：

/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */


CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON

SELECT QUOTENAME(USER_NAME(t.uid))+ '.' +  QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.xtype
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) AS CurrentIdentityValue,
CASE c.xtype
WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid)  + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM syscolumns AS c
INNER JOIN
sysobjects AS t
ON t.id = c.id
WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1
AND OBJECTPROPERTY(t.id, 'isTable') = 1
ORDER BY PercentageUsed DESC
END



/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */


CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON

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
END

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14321372/viewspace-614962/，如需轉載，請註明出處，否則將追究法律責任。