Microsoft Study Bible

In SQL Server database, we often used  IDENTITY property to define the columns of self-growth when creating the table .However, how can we effectively and quickly check the column out properly configured and the use of its percentage? After reading a lot of materials or information, I find this following sentences most convenient.

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

If this stored procedure was called in SQL Server 2000 ,the following errors will appear:

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

So ,in SQL Server 2000 ,those procedure should be changed:

/* 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

Share This