Find gaps in primary keys across multiple databases

,

What it does: 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 multiple databases that have a common purpose and help maintain standardization between them. It is very convenient that it can be run from a local server and pull and gather data from remote servers, including linked servers to other database types.

Use: The script is parameterized so it can be easily edited to run against different tables and PK's. Edit the variables at the top to specify the table and primary key id names. There is also a variable for defining the minimum gap size to look for. This script is designed for four databases: 2 running separate SQL Servers and two running on separate Sybase servers (accessed via a linked server). Note, that for the SQL Server instances, I found that I neeed to re-declare the variables inside the dynamic SQL query. This section should be edited to fit your situation. I suppose the next best thing would be to install as a parameterized stored procedure.

Kudos: With the help that I gleaned from Alexander Kozak's analysis of finding "islands and gaps in sequential numbers" (ref: http://msdn.microsoft.com/en-us/library/aa175780(v=sql.80).aspx), I am able to greatly simplify the task of finding gaps and groups of available PK's in tables across multiple systems allowing me to write "one-size-fits-all" scripts.

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

*/

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)