in src/PDWScripter/PDWscripter.cs [586:822]
private void getSourceColumns(Boolean getStruture, Boolean SourceFromFile)
{
cols.Clear();
distColumn = "";
columnClause = "";
StringBuilder columnSelect = new StringBuilder();
StringBuilder columnspec = new StringBuilder();
List<ColumnDef> tempCols = new List<ColumnDef>();
if (!SourceFromFile)
{
cmd.CommandText =
"select c.column_id, c.name, t.name as type, c.max_length, c.precision," +
"c.scale, c.is_nullable, d.distribution_ordinal, c.collation_name, ISNULL('DEFAULT '+dc.definition,'') as DefaultConstraint " +
"from sys.columns c " +
"join sys.pdw_column_distribution_properties d " +
"on c.object_id = d.object_id and c.column_id = d.column_id " +
"join sys.types t on t.user_type_id = c.user_type_id " +
"left join sys.default_constraints dc on c.default_object_id =dc.object_id and c.object_id =dc.parent_object_id " +
"where c.object_id = (select object_id from sys.tables where schema_name(schema_id) + '.' + name = '" + sourceTable + "') " +
"order by Column_Id ";
rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
cols.Add(new ColumnDef(
rdr.GetInt32(rdr.GetOrdinal("column_id")),
rdr.GetString(rdr.GetOrdinal("name")),
rdr.GetString(rdr.GetOrdinal("type")),
rdr.GetInt16(rdr.GetOrdinal("max_length")),
rdr.GetByte(rdr.GetOrdinal("precision")),
rdr.GetByte(rdr.GetOrdinal("scale")),
rdr.GetBoolean(rdr.GetOrdinal("is_nullable")),
rdr.GetByte(rdr.GetOrdinal("distribution_ordinal")),
rdr.GetString(rdr.GetOrdinal("DefaultConstraint")),
rdr["collation_name"] == DBNull.Value ? string.Empty : (string)rdr["collation_name"]
));
}
rdr.Close();
if (getStruture)
{
dbstruct.GetTable(sourceTable).Columns.AddRange(cols);
return;
}
}
}
else
{
cols = dbstruct.GetTable(sourceTable).Columns;
}
cols.Sort((a, b) => a.column_id.CompareTo(b.column_id));
int minColumnId = this.GetMinColumnId(this.cols);
foreach (ColumnDef c in cols)
{
StringBuilder columnDefinition = new StringBuilder();
if (c.distrbution_ordinal == 1)
{
// Save name of Distribution column
distColumn = c.name;
}
if (c.column_id > minColumnId)
{
columnspec.Append("\r\n\t,");
columnSelect.Append("\r\n\t,");
}
else
{
columnspec.Append("\t");
columnSelect.Append("\t");
}
columnDefinition.Append("[" + c.name + "]" + "\t" + c.type + "\t");
columnspec.Append("[" + c.name + "]" + "\t" + c.type + "\t");
columnSelect.Append(c.name);
if (c.type == "bigint" ||
c.type == "bit" ||
c.type == "date" ||
c.type == "datetime" ||
c.type == "int" ||
c.type == "smalldatetime" ||
c.type == "smallint" ||
c.type == "smallmoney" ||
c.type == "money" ||
c.type == "tinyint" ||
c.type == "real" ||
c.type == "uniqueidentifier")
{
// no size params
}
else if (
c.type == "binary" ||
c.type == "varbinary")
{
// max_length only
columnspec.Append("(");
columnspec.Append(GetMaxLength(c));
columnspec.Append(")\t");
columnDefinition.Append("(");
columnDefinition.Append(GetMaxLength(c));
columnDefinition.Append(")\t");
}
else if (
c.type == "char" ||
c.type == "varchar")
{
// max_length only
columnspec.Append("(");
columnspec.Append(GetMaxLength(c));
columnspec.Append(")\t");
columnspec.Append("COLLATE\t");
columnspec.Append(c.collation_name);
columnspec.Append("\t");
columnDefinition.Append("(");
columnDefinition.Append(GetMaxLength(c));
columnDefinition.Append(")\t");
columnDefinition.Append("COLLATE\t");
columnDefinition.Append(c.collation_name);
columnDefinition.Append("\t");
}
else if (
c.type == "nchar" ||
c.type == "nvarchar")
{
// max_length only
columnspec.Append("(");
columnspec.Append(GetMaxLength(c));
columnspec.Append(")\t");
columnspec.Append("COLLATE\t");
columnspec.Append(c.collation_name);
columnspec.Append("\t");
columnDefinition.Append("(");
columnDefinition.Append(GetMaxLength(c));
columnDefinition.Append(")\t");
columnDefinition.Append("COLLATE\t");
columnDefinition.Append(c.collation_name);
columnDefinition.Append("\t");
}
else if (
c.type == "float")
{
// precision only
columnspec.Append("(");
columnspec.Append(c.precision);
columnspec.Append(")\t");
columnDefinition.Append("(");
columnDefinition.Append(c.precision);
columnDefinition.Append(")\t");
}
else if (
c.type == "datetime2" ||
c.type == "datetimeoffset" ||
c.type == "time")
{
// Scale only
columnspec.Append("(");
columnspec.Append(c.scale);
columnspec.Append(")\t");
columnDefinition.Append("(");
columnDefinition.Append(c.scale);
columnDefinition.Append(")\t");
}
else if (
c.type == "decimal" ||
c.type == "numeric")
{
// Precision and Scale
columnspec.Append("(");
columnspec.Append(c.precision);
columnspec.Append(",");
columnspec.Append(c.scale);
columnspec.Append(")\t");
columnDefinition.Append("(");
columnDefinition.Append(c.precision);
columnDefinition.Append(",");
columnDefinition.Append(c.scale);
columnDefinition.Append(")\t");
}
else
{
Exception e = new Exception("Unsupported Type " + c.type + " for column : "+c.name+" - Table : "+ sourceTable);
throw e;
}
columnspec.Append(c.is_nullable ? "NULL" : "NOT NULL");
columnDefinition.Append(c.is_nullable ? "NULL" : "NOT NULL");
columnspec.Append(" " + c.defaultconstraint);
ColumnDef current = cols[cols.IndexOf(c)];
current.columnDefinition = columnDefinition.ToString();
tempCols.Add(current);
}
columnClause = columnspec.ToString();
cols = tempCols;
if (cols.Count == 0)
{
// invalid query
throw new Exception("Unable to retrieve column data for " + sourceTable + " in database " + sourceDb);
}
}