Help

Page tree

Reset Record ID Numbers

This Bash script is applicable for the MySQL/MSSQL platform. It will reset the ID for each table of the KB to 1 or the MaxID+1 if there exist some records in the table with MaxID being the ID for the last record.

Replace "Demo" with the actual KB name before executing it through the Admin Console at Setup > Debugging > Bean shell.

Note: If demo records are deleted using the Admin Console delete demo data functions, the "deleted" records leave a "null" record in the SQL DB, and this will block any attempt to reset ID numbers or to import directly to that ID number. Those empty SQL records can be deleted by support or developers. 

String projectName = "Demo";
import com.supportwizard.db.*;
import com.supportwizard.dictionary.*;
import com.supportwizard.utils.*;
import com.supportwizard.utils.db.*;
import java.sql.*;
 
StringBuilder log = new StringBuilder();
HomesGetter homes = new HomesGetter();
SWProjectHome projectHome = homes.getHome(SWProjectHome.class);
SWTableHome tableHome = homes.getHome(SWTableHome.class);
SWInternal_SQL sql = new SWInternal_SQL();
 
sql.dbConnect();
try {
for
 (SWTable table : 
tableHome.findByProjectAndType(projectHome.findByName(projectName).getSWProjectID(),
 SWTable.USER_OBJECT)) {
String autoincColumnDBName = null;
for (SWColumn column : table.getSWColumns()) {
if (column.isAutoIncrement()) {
autoincColumnDBName = column.getDBName();
break;
}
}
if (autoincColumnDBName == null) continue;
ResultSet
 rs = sql.getConnection().createStatement()
.executeQuery("select max("+autoincColumnDBName+") from "+table.getDBName());
int autoinc = 0;
if (rs.next()) autoinc = rs.getInt(1);
autoinc++;
log.append("setting autoinc="+autoinc+" for table="+table.getDBName()+"."+autoincColumnDBName+"\n");
if (DBSyntaxHelper.isMSSQL)
sql.getConnection().createStatement().execute("DBCC CHECKIDENT ('[" + table.getDBName() + "]', RESEED, " + autoinc + ")");
else
sql.getConnection().createStatement().execute("alter table "+table.getDBName()+" auto_increment=" + autoinc);
}
} finally {
sql.dbDisconnect();
}
return log;

 


 

 

CONTENTS