"SQL" finds tables with unequal rows and self-increment identity values (that is, there are missing rows)

Original: "SQL" finds tables with unequal row count and self-increment identity value (that is, there are missing rows)

Environment:MSSQL ENT 2k8 R2

principle: traverse all user tables with self-increment columns, get the number of rows per table and write to the temporary table using the sp_spaceused procedure, and 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#TableRowCountGOCREATE TABLE#TableRowCount (Name sysnamePRIMARY KEY, rowcntDECIMAL( One,0), ReservedVARCHAR( -), DataVARCHAR( -), index_sizeVARCHAR( -), UnusedVARCHAR( -))GODECLARECurcntalltablerowsCURSORLOCAL Fast_forward for        SELECTName fromSys.tablesWHERE ObjectProperty(object_id,'tablehasidentity')=1OPENcurcntalltablerowsDECLARE @TableNamesysnameFETCH NEXT  fromCurcntalltablerows into @TableName while @ @FETCH_STATUS=0BEGIN INSERT  into#TableRowCountEXECSys.sp_spaceused@TableName FETCH NEXT  fromCurcntalltablerows into @TableNameENDCLOSEcurcntalltablerowsdeallocatecurcntalltablerows/*since ident_current also returns 1 for empty tables, special handling is done*/ withCteA as (SELECTNAME,ROWCNT, CaseIdent_current (Name) when 1  ThenRowcntELSEIdent_current (Name)END  as 'Currid' from#TableRowCount)SELECTName,rowcnt,currid fromCteAWHERERowcnt<>Currid

Wenbi.

"SQL" finds tables with unequal rows and self-increment identity values (that is, there are missing rows)