Identify the Identity

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 syscolumnsWHERE (status & 128) = 128

This query also works on SQL Server 2000 and 7.0.