static SqlTable GetTableSchema(SqlConnection conn, string tableName)
{
string sql = $"sp_help '{tableName}'";
using SqlCommand cmd = new SqlCommand(sql, conn);
using SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
string?name = rdr.GetValue <string>("Name");
if (name == null)
{
throw new Exception("Table name was null");
}
SqlTable table = new SqlTable(name);
rdr.NextResult();
while (rdr.Read())
{
string? colName = rdr.GetValue <string>("Column_name");
SqlDbType dataType = ParseTools.Enum(rdr.GetValue <string>("Type"), SqlDbType.NVarChar);
bool computed = rdr.GetValue <string>("Computed") == "yes";
int length = rdr.GetValue <int>("Length");
bool nullable = rdr.GetValue <string>("Nullable") == "yes";
SqlColumn col = new SqlColumn(colName, dataType, computed, length, nullable);
table.Columns.Add(col);
}
rdr.NextResult();
while (rdr.Read())
{
string?identityCol = rdr.GetValue <string>("Identity");
if (!string.IsNullOrWhiteSpace(identityCol))
{
SqlColumn?col = table.Columns.FirstOrDefault(c => c.Name == identityCol);
if (col != null)
{
col.IsIdentity = true;
}
}
}
rdr.NextResult();
// rowguidcol table
rdr.NextResult();
rdr.NextResult();
// indexes table
while (rdr.Read())
{
if (rdr.HasColumn("index_name"))
{
string?indexName = rdr.GetValue <string>("index_name");
string?indexDescription = rdr.GetValue <string>("index_description");
string?indexKeys = rdr.GetValue <string>("index_keys");
if (indexDescription != null && indexKeys != null && indexDescription.Contains("primary key", StringComparison.InvariantCultureIgnoreCase))
{
string[] colNames = indexKeys.Split(new[] { ", " }, StringSplitOptions.RemoveEmptyEntries);
table.Columns.Where(c => colNames.Contains(c.Name)).ForEach(c => c.IsPrimaryKey = true);
}
}
}
return(table);
}
