Congratulations to Narasimhan Jayachandran, DBA at HTC Global Services in Southfield, Michigan, and Alexey Ruban, software engineer and lead developer at NewDamage Ukraine Ltd. in Dniepropetrovsk, Ukraine. Narasimhan won first prize of $100 for the best solution to the November Reader Challenge, "Identify the Identity." Alexey won second prize of $50. Watch SQL Server Magazine UPDATE for next month's Reader Challenge. In the meantime, here's the solution to the November Reader Challenge.

Problem
Stan manages several SQL Server installations (including SQL Server 2000, 7.0, and 6.5). He recently has had some interesting discussions with his colleagues about the use of surrogate keys versus natural keys and SQL Server's IDENTITY column property. Stan has decided to check some of his databases and find out the table names, with the seed (start value) and increment, of the tables that are using IDENTITY. Help Stan write scripts to find out which tables use the IDENTITY property and the seed and increment for each table. Stan avoids directly querying the system tables whenever possible.

Solution
Although the system tables hold the information that Stan’s scripts need, Stan’s SQL Server release dictates whether Stan can obtain the information without querying the system tables. To obtain the seed and increment values, Stan can sidestep the system tables in all three current releases, SQL Server 2000, 7.0, and 6.5, by using the IDENT_SEED and IDENT_INCR functions.

Detecting the presence of the IDENTITY property in a table is another matter. In SQL Server 2000 and 7.0, Stan can obtain the names of the tables that use IDENTITY indirectly by using the OBJECTPROPERTY function, which Microsoft introduced in SQL Server 7.0. The following query uses the INFORMATION_SCHEMA.TABLES view and the IDENT_SEED and IDENT_INCR functions to find the table names:

SELECT 
  IDENT_SEED(TABLE_NAME) AS Seed
, IDENT_INCR(TABLE_NAME) AS Increment
, TABLE_NAME
 FROM INFORMATION_SCHEMA.TABLES
 WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME),
 'TableHasIdentity') = 1
 AND   TABLE_TYPE = 'BASE TABLE'

However, SQL Server 6.5 doesn't include the OBJECTPROPERTY function or INFORMATION_SCHEMA views. To find out whether a table uses an IDENTITY function, Stan must query the system tables directly and look at the source code for sp_help. Stan can determine whether a column is using IDENTITY by checking the status column in the syscolumns table for bit mask 128. Here is a query that Stan can run on SQL Server 6.5 to retrieve the names of the tables that use the IDENTITY property and the seed and increment value for each table: 

SELECT 
  IDENT_SEED(OBJECT_NAME(id)) AS Seed
, IDENT_INCR(OBJECT_NAME(id)) AS Increment
, OBJECT_NAME(id) FROM syscolumns
WHERE (status & 128) = 128

This query also works on SQL Server 2000 and 7.0.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

right on

Anonymous User- June 21, 2005

Article Rating 5 out of 5

Grateful for the info. I needed a way to detect tables with IDENTITY columns and found this most useful. Thanks for introducing me to the OBJECTPROPERTY function as well.

Anonymous User- August 03, 2005

Article Rating 4 out of 5

Hi,

Please Guide me for the following issue,While executing the following code it is showing some warnings that ' (141 row(s) affected)

(141 row(s) affected)

Warning: The table 'processed_olap_data_1' has been created but its maximum row size (3873071) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes'. so is the any other to add the identity column to a table with out warnings....? Please suggest me.....

The Code is as following ------------------------- IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID('[DBO].[processed_olap_data_1]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1) DROP TABLE [DBO].[processed_olap_data_1] select * into dbo.processed_olap_data_1 from pubs.dbo.processed_olap_data_1 EXEC ('ALTER TABLE dbo.processed_olap_data_1 ADD generatedid INT IDENTITY')

Thanks, Rao Aregaddan.

aregaddanrao- January 22, 2006

Article Rating 4 out of 5