By querying the system dictionary table of Sybase, you can copy the table structure (including the primary key and index of the table)

Recently, when I was working on a project, I had such a requirement. Because the data volume of some tables is too large, I Would Like To generate a backup table every year to back up the data of the previous year to the newly generated backup table, because of the large amount of data, we need to consider the efficiency of data query. That is, simply copying the table structure without replicating the primary key and index cannot meet the requirements, I finally got it done. Now I have pasted the code and shared it with you. (if the code is not well written, you will be watching it ).

Package platform. business. data. client. database; <br/> import java. SQL. connection; <br/> import java. SQL. driverManager; <br/> import java. SQL. resultSet; <br/> import java. SQL. SQLException; <br/> import java. SQL. statement; <br/> import org. apache. commons. lang. stringUtils; <br/>/** <br/> * @ author Calr He <br> <br/> * creation date: 2010-05-08 <br/> */<br/> public class DBToolkit {<br/>/** <br/> * Query The result is inserted into a new table. If newTable already exists, newTable is deleted and then created again. <Br/> * @ param fromTable <br/> * @ param newTable <br/> * @ param st <br/> * @ param stmt1 <br/> * @ param where where condition for querying records, for example: where field = 'A' <br/> * @ throws SQLException <br/> * @ author Calr He <br/> */<br/> public static void createOldTableFromTable (String fromTable, string newTable, Statement st, Statement stmt1, String where) throws SQLException {<br/> String SQL = null; <br/> // If newTable already exists If yes, delete <br/> SQL = "if exists (select 1 from sysobjects where id = object_id ('" + newTable + "') and type = 'U ') drop table "+ newTable; <br/> st.exe cute (SQL); </p> <p> if (where = null) <br/> where = ""; <br/> // Insert the queried records to newTable <br/> SQL = "select * into" + newTable + "from" + fromTable + where; <br/> st.exe cute (SQL); </p> <p> int indid = 0; // value of the indid field of the primary key or index in the system table sysindexes <br/> int keycnt = 0 ;// Number of columns involved in the primary key or index. If the primary key index or common index is not a cluster index (determined by the 0x10 digit in status, the keycnt-1 <br/> String columnNames = null; // name of the primary key or column involved in the index </p> <p> // query the table's primary key <br/> SQL = "select indid, keycnt from sysindexes where status & 2048 = 2048 and id = object_id ('"+ fromTable +"') "; <br/> ResultSet rs = st.exe cuteQuery (SQL ); <br/> if (rs. next () {// you have set the primary key <br/> indid = rs. getInt (1); // the ID of the primary key index <br/> keycnt = rs. getInt (2); // Number of columns involved in the primary key. If the primary key index is not a cluster index (S 0x10), is the keycnt-1 <br/> columnNames = getFieldNamesOfRelatedIndexByIndid (indid, keycnt, fromTable, st ); <br/> SQL = "ALTER TABLE" + newTable + "ADD CONSTRAINT PK _" + newTable + "PRIMARY KEY (" + columnNames + ")"; <br/> st.exe cute (SQL); // create a primary key for the new table <br/>}< br/> rs. close (); </p> <p> // query the table index <br/> SQL = "select indid, keycnt, name from sysindexes where status & 2048! = 2048 and indid> 0 and indid <255 and id = object_id ('"+ fromTable +"') "; <br/> rs = st.exe cuteQuery (SQL ); <br/> ResultSet rs1; <br/> String unique = ""; // indicates whether the index is unique. <br/> String clustered = null; // keywords for cluster index or non-cluster index <br/> String indexName = null; // index name <br/> while (rs. next () {<br/> indid = rs. getInt (1); <br/> keycnt = rs. getInt (2); <br/> indexName = rs. getString (3); // index name </p> <p> // whether it is a unique constraint <br/> SQL = "sel Ect v. name from master. dbo. spt_values v, sysindexes I where I. status & v. number = v. number and v. type = 'I' and v. number = 2 and I. id = object_id ('"+ fromTable +"') "+" and I. indid = "+ indid; <br/> rs1 = stmt1.executeQuery (SQL); <br/> if (rs1.next () {<br/> unique = StringUtils. trimToEmpty (rs1.getString (1); <br/>}else {<br/> unique = ""; <br/>}< br/> rs1.close (); </p> <p> // whether the index is a cluster index <br/> if (indid = = 1) {<br/> clustered = "clustered"; // cluster <br/>} else {<br/> SQL = "select count (1) from sysindexes I where status2 & 512 = 512 and I. indid = "+ indid +" and I. id = object_id ('"+ fromTable +"') "; <br/> rs1 = stmt1.executeQuery (SQL); <br/> if (rs1.next () & rs1.getInt (1)> 0) {<br/> clustered = "clustered "; // cluster set <br/>} else {<br/> clustered = "nonclustered"; // non-cluster set <br/>}< br/> rs1.close (); <br/>} </P> <p> columnNames = getFieldNamesOfRelatedIndexByIndid (indid, keycnt, fromTable, stmt1 ); <br/> SQL = "create" + unique + clustered + "index" + indexName + "on" + newTable + "(" + columnNames + ")"; <br/> stmt1.executeUpdate (SQL ); // create an index for the new table <br/>}</p> <p>/** <br/> * query the name of the column involved in the primary key or index <br/> * @ param indid primary key or the value of the indid field of the index in the system table sysindexes <br/> * @ param keycnt primary key or the number of columns involved in the index <br/> * @ param t The table where the ableName index is located <br/> * @ param st executes the java. SQL. statement object <br/> * @ return the name of the primary key or column involved in the index (if multiple columns exist, the names of each column are connected by commas (,), for example: field_0 or field_1, field_2, field_3 <br/> * @ throws SQLException <br/> * @ author Calr He <br/> */<br/> private static String getFieldNamesOfRelatedIndexByIndid (int indid, int keycnt, string tableName, Statement st) throws SQLException {<br/> StringBuffer buff = new StringBuffer (); <B R/> buff. append ("select"); <br/> for (int I = 1; I <= keycnt; I ++) {<br/> if (I> 1) <br/> buff. append (","); <br/> buff. append ("index_col ('"). append (tableName ). append ("',"). append (indid ). append (","). append (I ). append (")"); <br/>}< br/> buff. append ("from sysindexes where indid = "). append (indid ). append ("and id = object_id ('"). append (tableName ). append ("')"); </p> <p> ResultSet rs = st.exe cuteQuery (buff. toStrin G (); // query the names of the columns involved in the primary key </p> <p> buff = new StringBuffer (); <br/> if (rs. next () {<br/> String columnName = null; <br/> for (int I = 1; I <= keycnt; I ++) {<br/> columnName = StringUtils. trimToNull (rs. getString (I); <br/> if (columnName = null) <br/> break; <br/> buff. append (columnName ). append (","); <br/>}< br/> rs. close (); </p> <p> int length = buff. length (); <br/> return length = 0? Buff. toString (): buff. substring (0, length-1); <br/>}</p> <p> public static void main (String [] args) {</p> <p> try {<br/> Class. forName ("com. sybase. jdbc2.jdbc. sybDriver "); <br/> Connection con = DriverManager. getConnection ("jdbc: sybase: Tds: 192.168.0.201: 5000/jcbase? Charset = cp850 & CHARSET_CONVERTER_CLASS = com. sybase. jdbc2.utils. cp850PureConverter "," sa "," 123456 "); <br/> Statement st = con. createStatement (); <br/>/* <br/> // query the SQL statement used to create a view based on the view name <br/> ResultSet rs = st.exe cuteQuery ("select text from sysobjects t, syscomments m where type = 'V' and m. id = t. id and t. name = 'v _ VIEW_NAME '"); <br/> while (rs. next () <br/> System. out. println (rs. getString (1); <br/> */<br/> createOldTableFromTable ("T_TABLE_NAME", "T_TABLE_NAME_2010", st, con. createStatement (), "WHERE 1 = 2"); <br/>} catch (Exception e) {<br/> e. printStackTrace (); <br/>}< br/>