{
Connection c = conn.getConnection();
StringBuilder buf = new StringBuilder();
TableConstraints constraints = MssqlIntrospector.getTableConstraints(oi, conn);
CallableStatement stmt = null;
ResultSet rs = null;
try
{
stmt = c.prepareCall("{ call sp_help ? }");
stmt.setString(1, oi.getSimpleName());
if (!stmt.execute()) return null;
/* since .execute() returned true, the first result is a ResultSet. */
rs = stmt.getResultSet();
/* Name Owner Type Created_datetime
* ---------------------------------------------------------
* billing dbo user table 2004-03-08 10:41:05.030
*/
if (!rs.next()) return null;
buf.append("CREATE TABLE [");
buf.append(rs.getString(2));
buf.append("].[");
buf.append(rs.getString(1));
buf.append("] (");
buf.append("\n");
if (!stmt.getMoreResults()) return null;
rs = stmt.getResultSet();
/* Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
* -------------------------------------------------------------------------------------------------------------------------------------------------------
* Location char no 2 yes no yes Latin1_General_CI_AS
* TotalBilledAmnt money no 8 19 4 yes (n/a) (n/a) NULL
*/
while (rs.next())
{
String colName = rs.getString(1);
String colType = rs.getString(2);
buf.append("\t[");
buf.append(colName);
buf.append("] [");
buf.append(colType);
buf.append("] ");
if (colType.equals("char") || colType.equals("varchar"))
{
buf.append("(");
buf.append(rs.getInt(4)); // length
buf.append(") COLLATE ");
buf.append(rs.getString(10)); // collation
buf.append(" ");
}
if (rs.getString(7).equals("yes")) buf.append("NULL ");
else buf.append("NOT NULL ");
if (withConstraints)
{
List<DefaultConstraint> defs = constraints.getDefaultsForColumn(colName);
/* there can be only one default in truth, but the model allows more than one. */
if (defs != null && defs.size() == 1)
{
DefaultConstraint def = defs.get(0);
buf.append("CONSTRAINT [");
buf.append(def.getConstraintName());
buf.append("] DEFAULT ");
buf.append(def.getDefaultExpression());
buf.append(" ");
}
}
buf.append(",\n");
}
}
catch (SQLException e)
{
s_log.error("generateCreateTableScript: Unexpected exception - " + e.getMessage(), e);
}
finally
{
closeResultSet(rs);
closeStatement(stmt);
}
if (withConstraints)
{
/* there can be only one PK in truth, but the model allows more than one. */
List<PrimaryKeyConstraint> pks = constraints.getPrimaryKeyConstraints();
if (pks != null && pks.size() == 1)
{
PrimaryKeyConstraint pk = pks.get(0);
buf.append("\tCONSTRAINT [");
buf.append(pk.getConstraintName());
buf.append("] PRIMARY KEY ");
buf.append(pk.isClustered() ? "CLUSTERED" : "NONCLUSTERED");
buf.append("\n\t(\n\t\t");
Object[] cols = pk.getConstraintColumns();
for (int i = 0; i < cols.length; i++)
{
buf.append("[");
buf.append((String) cols[i]);
buf.append("]");
if (i < cols.length - 1) buf.append(", ");
}
buf.append("\n\t)\n");
/* TODO: FILLFACTOR, ON [PRIMARY], etc. */
}
List<ForeignKeyConstraint> fks = constraints.getForeignKeyConstraints();
for (int i = 0; i < fks.size(); i++)
{
ForeignKeyConstraint fk = fks.get(i);
buf.append("\tFOREIGN KEY\n\t(\n\t\t");
Object[] foreignColumns = fk.getConstraintColumns();
for (int j = 0; j < foreignColumns.length; j++)
{
buf.append("[");
buf.append((String) foreignColumns[j]);
buf.append("]");
if (j < foreignColumns.length - 1) buf.append(", ");
}
buf.append("\n\t) REFERENCES [");
buf.append(fk.getReferencedTable());
buf.append("] (\n\t\t");
Object[] primaryColumns = fk.getPrimaryColumns();
for (int j = 0; j < primaryColumns.length; j++)
{
buf.append("[");
buf.append((String) primaryColumns[j]);
buf.append("]");
if (j < primaryColumns.length - 1) buf.append(",\n");
}
buf.append("\n\t),");
}
for (CheckConstraint check : constraints.getCheckConstraints())
{
buf.append("\tCONSTRAINT [");
buf.append(check.getConstraintName());
buf.append("] CHECK ");
buf.append(check.getCheckExpression());