Finding Auto Increment value for each table in DB

  • sizal0234

    SSCrazy

    Points: 2230

    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;

     

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 721457

    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;
  • oogibah

    SSC Eights!

    Points: 802

    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: 2230

    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: 802

    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: 2230

    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: 802

    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: 721457

    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.

     

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

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