Finding Auto Increment value for each table in DB

Question

Finding Auto Increment value for each table in DB

sizal0234

SSCrazy

Points: 2272
More actions
December 23, 2019 at 4:50 pm

#3710031

Hi,
I am trying to get the max value of auto identity column with table name and column name. Below is the query I am using which GIVES me table and column name BUT NOT the MAX value.
SELECT
OBJECT_SCHEMA_NAME(tables.object_id, db_id())
AS SchemaName,
tables.name As TableName,
columns.name as ColumnName
FROM sys.tables tables
JOIN sys.columns columns
ON tables.object_id=columns.object_id
WHERE columns.is_identity=1
THis is a separate query for just 1 to get the MAX value of columns found in above query...I am unable to combine both and feel there should be a way...please let me know. Thanks!
select max(COL NAME) from dbo.TBLNAME;

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

Are you looking for the max value in a table or are you looking for the last value used? (or next one needed)?

These are different things. The identity doesn't ensure you use some max value. Identities can be deleted as well, so depending on what you are trying to do, this might not be what you want.

There is a sys.identity_columns DMV. You can add this with:

SELECT
               SchemaName = OBJECT_SCHEMA_NAME(tables.object_id, DB_ID())
             , TableName = tables.name
             , ColumnName = columns.name
             , ic.last_value
FROM
               sys.tables AS tables
    INNER JOIN sys.columns AS columns
        ON tables.object_id = columns.object_id
    INNER JOIN sys.identity_columns AS ic
        ON ic.column_id     = columns.column_id
           AND ic.object_id = columns.object_id
WHERE          columns.is_identity = 1;

This reply was modified 4 years, 3 months ago by Steve Jones - SSC Editor.

oogibah SSC Eights! Points: 827 More actions · Answer 2

If you do need the max value instead of the last value.. here is a very ugly query that somebody should probably yell at me for writing, its just the way I know how to do it.

#SorryJeff

DECLARE @Schema sysname;
DECLARE @TableName sysname;
DECLARE @ColumnName sysname;
DECLARE @SQL NVARCHAR(250);
DECLARE @MaxValue INT;

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;
CREATE TABLE #TempTable (SchemaName sysname, TableName sysname, ColumnName sysname, MaxValue INT);

INSERT INTO #TempTable (SchemaName, TableName, ColumnName)
SELECT
    OBJECT_SCHEMA_NAME(tables.object_id, DB_ID()) AS SchemaName
  , tables.name AS TableName
  , columns.name AS ColumnName
FROM
    sys.tables tables
    JOIN sys.columns columns
        ON tables.object_id = columns.object_id
WHERE
    columns.is_identity = 1;

WHILE EXISTS (SELECT TOP (1) @ColumnName FROM #TempTable WHERE MaxValue IS NULL)
BEGIN
    SELECT TOP (1)
           @TableName = TableName
    FROM
        #TempTable
    WHERE
        MaxValue IS NULL
    ORDER BY
        TableName DESC;
    SELECT TOP (1)
           @Schema = SchemaName
    FROM
        #TempTable
    WHERE
        TableName = @TableName;
    SELECT TOP (1)
           @ColumnName = ColumnName
    FROM
        #TempTable
    WHERE
        TableName = @TableName;

    SET @SQL = N'SELECT @MaxValue= Max(' + @ColumnName + N') FROM ' + @Schema + N'.' + @TableName + N'';
    EXEC sp_executesql
        @SQL
      , N'@MaxValue INT out'
      , @MaxValue = @MaxValue OUT;

    IF @MaxValue IS NULL
        SET @MaxValue = '0';

    UPDATE
        #TempTable
    SET
        MaxValue = @MaxValue
    WHERE
        TableName = @TableName;

    SET @ColumnName = '';
    SET @TableName = '';
    SET @MaxValue = '';
END;

SELECT
    *
FROM
    #TempTable;

sizal0234 SSCrazy Points: 2272 More actions · Answer 3

Thanks...I was looking for MAX value. I used the same query and looks like it gives current value(latest value)...I am find with current value as well.

oogibah SSC Eights! Points: 827 More actions · Answer 4

My query should work for you then, hopefully somebody will come by with a smarter/more efficient query though.. I'd be interested in seeing the right way to do it.

sizal0234 SSCrazy Points: 2272 More actions · Answer 5

I used IDENT_CURRENT(tables.name) to fetch the current max value.

SELECT

tables.name As TableName,

columns.name as ColumnName,

IDENT_CURRENT(tables.name)

FROM sys.tables tables

JOIN sys.columns columns

ON tables.object_id=columns.object_id

WHERE columns.is_identity=1

order by TableName

oogibah SSC Eights! Points: 827 More actions · Answer 6

Interesting, returns some Null for me where there shouldnt be, but It makes sense.. I would point out that would necessarily give you the MAX it just gives you what the current value is set to, its the equivalent of what Steve posted.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

The identity is not necessarily the max value. I can use identity_insert or dbcc checkident(reseed) and end up with different values. The max in the table might be far different from the last/next value for identity.