Find gaps in primary keys across multiple databases

-- This script polls multiple databases to find gaps in a Primary Key ID in a table common to multiple databases.


-- It is useful not only for finding gaps in PK's, but also for finding a single PK or group of PK's that are

-- available in all databases. This is handy for being able to create a single script for adding new records 

-- that can run on all databases and help maintain standardization between them.

-- It is very convenient in that it can be run from a local server and pull and gather data from remote servers,

-- including linked servers to other database vendors.

-- Last Updated: August 12, 2011



USE [tempdb]



DECLARE @KeyIDName varchar(20)

DECLARE @TableName varchar(20)

DECLARE @MinGapSize int

SET @KeyIDName = 'KeyID'            -- Replace with Primary Key ID

SET @TableName = 'TABLE'            -- Replace with Table Name

SET @MinGapSize = 1                    -- Set desired minimum gap sizes to include in output



IF object_id('#ALL_KeyIDs') IS NOT NULL DROP TABLE #ALL_KeyIDs



CREATE TABLE #ALL_KeyIDs (KeyID INT)



---------------------------------------------------------------------

-- SQL Server DATABASE1 located on SERVER1\SQL1

---------------------------------------------------------------------

EXEC ('

     DECLARE @KeyIDName varchar(20)

     DECLARE @TableName varchar(20)

     INSERT INTO #ALL_KeyIDs

     SELECT '+@KeyIDName+'

     FROM [SERVER1\SQL1].DATABASE1.dbo.'+@TableName+' WHERE '+@KeyIDName+' >= 0        -- Optional WHERE clause captures positve KeyIDs only

     ');



---------------------------------------------------------------------

-- SQL Server DATABASE2 located on SERVER2\SQL1

---------------------------------------------------------------------

EXEC ('

     DECLARE @KeyIDName varchar(20)

     DECLARE @TableName varchar(20)

     INSERT INTO #ALL_KeyIDs

     SELECT '+@KeyIDName+'

     FROM [SERVER2\SQL1].DATABASE2.dbo.'+@TableName+' WHERE '+@KeyIDName+' >= 0    -- Optional WHERE clause captures positve KeyIDs only

     ');



---------------------------------------------------------------------

-- Sybase DATABASE3 accessed via Linked Server SYBASE_SERVER1

---------------------------------------------------------------------

EXEC ('

     INSERT INTO #ALL_KeyIDs

     SELECT '+@KeyIDName+'

     FROM OPENQUERY([SYBASE_SERVER1],

        ''SELECT '+@KeyIDName+' FROM '+@TableName+' WHERE '+@KeyIDName+' >= 0'')    -- Optional WHERE clause captures positve KeyIDs only

    ');



---------------------------------------------------------------------

-- Sybase DATABASE4 accessed via Linked Server SYBASE_SERVER2

---------------------------------------------------------------------

EXEC ('

     INSERT INTO #ALL_KeyIDs

     SELECT '+@KeyIDName+'

     FROM OPENQUERY([SYBASE_SERVER2],

        ''SELECT '+@KeyIDName+' FROM '+@TableName+' WHERE '+@KeyIDName+' >= 0'')    -- Optional WHERE clause captures positve KeyIDs only

     ');



--SELECT * FROM #ALL_KeyIDs GROUP BY KeyID ORDER BY KeyID 



-- Ref: http://msdn.microsoft.com/en-us/library/aa175780(v=sql.80).aspx by Alexander Kozak



-- Output gaps in KeyIDs

SELECT BeginGap = c1 + 1, EndGap = c2 - 1, GapSize = c2 - c1 - 1

FROM 

    (

     SELECT t1.KeyID AS c1, MIN(t2.KeyID) AS c2 

     FROM #ALL_KeyIDs t1 INNER JOIN #ALL_KeyIDs t2 ON t1.KeyID < t2.KeyID

     GROUP BY t1.KeyID

    ) t3 WHERE c2-c1 > 1 AND (c2 - c1 - 1) >= @MinGapSize

ORDER BY BeginGap



/*

-- Output groups of KeyIDs

SELECT t1.KeyID AS BeginGroup, MIN(t2.KeyID) AS EndGroup, MIN(t2.KeyID) - t1.KeyID + 1 AS Groupsize

FROM

    (

     SELECT KeyID FROM #ALL_KeyIDs tbl1 

     WHERE NOT EXISTS(SELECT * FROM #ALL_KeyIDs tbl2 WHERE tbl1.KeyID = tbl2.KeyID + 1)

    ) t1

    INNER JOIN 

    (

     SELECT KeyID FROM #ALL_KeyIDs tbl1 

     WHERE NOT EXISTS(SELECT * FROM #ALL_KeyIDs tbl2 WHERE tbl2.KeyID = tbl1.KeyID + 1)

 ) t2

    ON t1.KeyID <= t2.KeyID

GROUP BY t1.KeyID

*/