public static string WriteTableRow(MDTableRow row)
{
var datatype = row.CharacterMaximumLength == "" || row.CharacterMaximumLength == "2147483647"
? row.DataType : row.DataType + "(" + row.CharacterMaximumLength + ")";
var str = string.Format("|{0}|{1}|{2}|{3}|{4}|{5}|"
, row.ColumnName
, datatype
, row.IsPrimaryKey ? "PK" : string.Empty
, row.IsNullable == "NO" ? "false" : "true"
, row.ColumnDefault
, row.Desc
);
return(str);
}
static void Main(string[] args)
{
var path = AppDomain.CurrentDomain.BaseDirectory + FileName;
File.Delete(path);
var tables = ExecuteDataTable("select * from INFORMATION_SCHEMA.TABLES");
var tableNames = new List <string>();
foreach (DataRow tableName in tables.Rows)
{
tableNames.Add((string)tableName["TABLE_NAME"]);
tableNames.Sort();
}
foreach (var tableName in tableNames)
{
var tableDesc = string.Empty;
var filterTable = true;
DataTable tableProperties = ExecuteDataTable("select name,value from sys.extended_properties where OBJECT_NAME(major_id)=@tablename and minor_id=0", new SqlParameter("tablename", tableName));
if (tableProperties.Rows.Count > 0)
{
foreach (DataRow item in tableProperties.Rows)
{
if (item["name"].ToString() == "desc")
{
tableDesc = item["value"].ToString();
}
else if (item["name"].ToString() == "effect")
{
filterTable = !Convert.ToBoolean(item["value"]);
}
}
}
if (filterTable)
{
continue;
}
DataTable tableColumns = ExecuteDataTable("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename", new SqlParameter("tablename", tableName));
DataTable tableColumnProperties = ExecuteDataTable("select objname as name, value from fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tablename, 'column', default)", new SqlParameter("tablename", tableName));
DataTable tablePKColumns = ExecuteDataTable("EXEC sp_pkeys @table_name=@tablename", new SqlParameter("tablename", tableName));
var pkPropertyName = string.Empty;
if (tablePKColumns.Rows.Count > 0)
{
pkPropertyName = tablePKColumns.Rows[0]["COLUMN_NAME"].ToString();
}
var Rows = new List <string>();
Rows.Add("### " + tableName + " " + tableDesc);
Rows.Add("");
Rows.Add("| 字段| 类型|主键|允许空|默认值|描述|");
Rows.Add("|-----|-----|----|------|------|----|");
foreach (DataRow column in tableColumns.Rows)
{
var name = (string)column["COLUMN_NAME"];
var desc = string.Empty;
foreach (DataRow item in tableColumnProperties.Rows)
{
if (item["name"].ToString() == name)
{
desc = item["value"].ToString();
break;
}
}
var row = new MDTableRow();
row.ColumnName = name;
row.IsPrimaryKey = pkPropertyName == name;
row.DataType = column["DATA_TYPE"].ToString();
row.Desc = desc;
row.CharacterMaximumLength = column["CHARACTER_MAXIMUM_LENGTH"].ToString();
row.ColumnDefault = column["COLUMN_DEFAULT"].ToString();
row.IsNullable = column["IS_NULLABLE"].ToString();
Rows.Add(WriteTableRow(row));
}
Rows.Add("");
Rows.Add("");
File.AppendAllLines(path, Rows);
Console.WriteLine(tableName + " 生成完成");
}
Console.WriteLine();
Console.WriteLine("生成结束");
Console.ReadKey();
}
