SQL SERVER – List All Missing Identity Values of Table in Database

The best part of any blog is when readers ask each other questions. Better still, is when a reader takes the time to provide a detailed response.

A few days ago, one of my readers, Yasmin, asked a very interesting question:

How we can find the list of tables whose identity was missed (not is sequential order) within the entire database?

A big thank you to SQL Server Expert, Imran Mohammed, for his excellent response to this question. He also provided an extremely impressive script, which is well described and contains inline comments.

We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.

This script is well worth keeping in your archive for future reference.

If you and use the script, please comment here and let me know about your experience.

If you know any other way that this can be accomplished or have a different solution, please post your comment here, or send me an email and I will publish it with due credit.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQLAuthority News – Search SQL Server Solutions
Next Post
SQL SERVER – 2008 – Design Process Decision Flow

Related Posts

41 Comments. Leave new

  • This was just what I was looking for…though I wanted the details for just 1 table..and I tweeked it accordingly…but the logic was ready and available
    This was of great help…

    Reply
  • While we are migrating master data from one server to another identity column place major role. We might need the same values as IDs to be uploaded to production server from devserver. If production server table misses some identity values that are there in dev server then we may need to identify missing idenity columns

    Reply
  • SET NOCOUNT ON

    –Create an table in which we can add identity and Values.
    IF OBJECT_ID(‘tblNumbers’) IS NOT NULL
    BEGIN
    DROP TABLE tblNumbers
    END

    CREATE TABLE tblNumbers
    (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    VALUE VARCHAR(20)

    )

    –Create an table in which we can add numbers.
    IF OBJECT_ID(‘tblAllNumbers’) IS NOT NULL
    BEGIN
    DROP TABLE tblAllNumbers
    END

    CREATE TABLE tblAllNumbers
    (
    NUMBER INT PRIMARY KEY

    )
    –Create an Number table upto 15,00,000
    INSERT INTO tblAllNumbers

    SELECT TOP 1500000 ROW_NUMBER() OVER(ORDER BY SC.ID) FROM sys.sysobjects SC , Sys.sysobjects SC1

    DECLARE @int INT,@MaxRowXount INT

    –Insert Row data into tblNumbers
    SELECT @int = 1

    WHILE @int 0

    SELECT @MaxRowXount = MAX(ID) FROM tblNumbers

    –Now Check for Missing Identity.

    SELECT TA.NUMBER FROM tblAllNumbers TA LEFT JOIN tblNumbers TN

    ON
    TA.NUMBER = TN.ID

    WHERE
    TA.NUMBER <= @MaxRowXount AND
    TN.ID IS NULL
    ORDER BY
    TA.NUMBER

    Reply
  • you can do this with Recursive CTE also,

    Declare @t table (ColId varchar (20))
    insert into @t
    Select ‘EC0001’ union all
    Select ‘EC0012’

    ;With CTE as
    (Select CAST(STUFF(MAX(ColId), 1,2,”) AS INT) colid from @t
    union all
    Select colid-1 colid from CTE where (colid-1)>=1 )
    Select ‘EC’+ replicate(‘0’,6-len(min(colid)))+cast(min(colid) as varchar) from CTE where
    colid not in (Select stuff(colid,1,2,null) from @t)

    Reply
  • Sunil Somani
    July 5, 2011 6:53 pm

    DECLARE @ID INT
    DECLARE @MaxID INT
    DECLARE @MissingCarTypeIDs TABLE ( [ID] INT )

    SELECT @MaxID = [ID] FROM Missingids

    SET @ID = 1
    WHILE @ID <= @MaxID
    BEGIN
    IF NOT EXISTS (SELECT 'X' FROM Missingids
    WHERE [ID] = @ID)
    INSERT INTO @MissingCarTypeIDs ( [ID] )
    VALUES ( @ID )

    SET @ID = @ID + 1
    END

    SELECT * FROM @MissingCarTypeIDs

    Reply
  • SELECT repeating_payment_id+1
    FROM pmnt_repeating_payment PRP1
    WHERE NOT EXISTS
    ( SELECT repeating_payment_id FROM pmnt_repeating_payment PRP2 WHERE PRP2.repeating_payment_id = PRP1.repeating_payment_id+1 )
    EXCEPT
    SELECT MAX(repeating_payment_id) + 1
    FROM pmnt_repeating_payment

    Reply
  • SELECT ID+1 FROM Tablename T1
    WHERE NOT EXISTS
    ( SELECT ID FROM Tablename T2 WHERE T2.ID = T1.ID+1 )
    EXCEPT
    SELECT MAX(ID)+1 FROM Tablename

    Reply
  • How to exclude data if Type in (‘A’, ‘B’) within same ID_Code and CreateDate columns. Here is value;

    ID_Code Type CreateDate
    1 A 7/1/2011
    1 B 7/5/2011
    1 C 7/2/2011
    2 A 7/12/2011
    2 B 7/12/2011
    2 D 7/14/2011
    3 A 7/12/2011

    Expected excluded data results should be this;

    2 A 7/12/2011
    2 B 7/12/2011
    2 D 7/14/2011

    Reply
    • Try this

      select * from @t where ID_Code in
      (
      select ID_Code from @t
      where type in (‘A’,’B’)
      group by ID_Code
      having MIN(CreateDate)=MAX(CreateDate)
      and COUNT(ID_Code)=2
      )

      Reply
  • The link to the above script is broken. Please fix it

    Reply
  • Prasobh Krishnan
    August 17, 2012 11:24 am

    I have a Windows Application to manage Battery Sales.
    I have a db table ,named as “Table_Sales”, inwhich “InvoiceId” is set to the Primary key.
    The same field is set with “Identity Specification” to “Yes” inwhich “Identity Increment” as “1” and “Identity Seed” as “1”.
    But , the “InvoiceId” with value “482” is missing.
    There is no option to delete from the table in the application.
    Only we are inserting values to the table.
    What May be the reason to miss one of the row?
    How can i insert a new row with Invoice Id 482?

    Thanks,
    Prasobh Krishnan

    Reply
  • How can i insert a new row with Invoice Id 482?

    This is easy,you can turn off identity before insert,after insert successful turn on the indentity.

    Are you sure you are missing only 482 from the sequence ?Or there are some other missing number too.

    Reply
  • I cannot find the link to download the script so please let me know.

    Reply

Leave a Reply

Menu