SYSK 124: IDENT_CURRENT Without an Identity Column

SQL Server’s function IDENT_CURRENT(‘tablename’) returns the last identity value set on a specified table.  While this is very useful, sometimes you need to get the next available primary key on a column that is not an identity field.  In this case,

SELECT IDENT_CURRENT (‘tablename’) will return NULL instead of the value you’re looking for.  If you find yourself in that situation, you should be able to use the code below to get the desired results:




DECLARE @Command nvarchar(512)

SET @Command = N'SELECT MAX(' + @TableName + '.' + @IdentityColumn +

      N') FROM ' + @TableName + ' WITH (XLOCK)';




EXEC sp_executesql @Command




Just create a function or stored procedure with the code above, and pass in the table name and the numeric column of your choice.


Comments (3)

  1. Siderite says:

    This works in SQL 2005 only, doesn’t it?

  2. irenake says:

    I believe ident_current is available in sql 2000, but not in sql 7

  3. chauhankamal says:

    Where is the information stored or maintained in the database, returned by IDENT_CURRENT function?

    Can we modify the vaue returned by this function?

Skip to main content