cool hit counter [SQL] Find the table whose number of rows is not equal to the value of the self-incrementing identifier (i.e., there are missing rows)_Intefrankly

[SQL] Find the table whose number of rows is not equal to the value of the self-incrementing identifier (i.e., there are missing rows)


Environment.mssql ent 2k8 r2

Principle. Iterate through all user tables containing self-incrementing columns, use the sp_spaceused procedure to get the number of rows in each table separately and write them to a temporary table, then use the IDENT_CURRENT function to get the maximum identification value of the table, compare the two and return the result

IF OBJECT_ID('tempdb..#TableRowCount','U') IS NOT NULL DROP TABLE #TableRowCount
GO
CREATE TABLE #TableRowCount (Name sysname PRIMARY KEY, RowCnt DECIMAL(11,0), Reserved VARCHAR(18), Data VARCHAR(18), Index_Size VARCHAR(18), Unused VARCHAR(18))
GO
DECLARE curCntAllTableRows CURSOR LOCAL FAST_FORWARD FOR
        SELECT name FROM sys.tables WHERE OBJECTPROPERTY(object_id,'TableHasIdentity')=1
OPEN curCntAllTableRows
DECLARE @TableName sysname
FETCH NEXT FROM curCntAllTableRows INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
 INSERT INTO #TableRowCount EXEC sys.sp_spaceused @TableName
 FETCH NEXT FROM curCntAllTableRows INTO @TableName
END
CLOSE curCntAllTableRows
DEALLOCATE curCntAllTableRows

/* thanks toIDENT_CURRENT It also returns for empty tables1, So special treatment*/
WITH cteA AS (
SELECT Name,RowCnt,CASE IDENT_CURRENT(Name) WHEN 1 THEN RowCnt ELSE IDENT_CURRENT(Name) END AS 'CurrID'
FROM #TableRowCount
)
SELECT Name,RowCnt,CurrID
FROM cteA
WHERE RowCnt<>CurrID

Text Bi.


Recommended>>
1、Stop looking at the blockchain and figure out the hash first
2、Is there really a soul in the world And where will the magical spirits gather
3、Happy Chinese New Year from NCTG Be happy and well
4、Alpha Dog Yuan beats its brother 0100 Go 9dan player Ke Jie says humans are too redundant
5、with respect toMySQL General query log and slow query log analysis

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号