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)
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…
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
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
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)
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
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
if 1st num is missing its not showing??
eg:
2,3,5,6,7
id
—
4
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
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
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
)
The link to the above script is broken. Please fix it
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
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.
I cannot find the link to download the script so please let me know.