/// <summary>
/// 获取所有的表信息包括表注释信息
/// </summary>
/// <returns></returns>
public static Dictionary <string, Dictionary <string, string> > TableinfoList()
{
Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
DataTable tables = UtilSqlserver.SqlExecute(Sql_Table_Comment);
if ((tables == null) || (tables.Rows.Count <= 0))
{
tables = UtilSqlserver.SqlExecute(Sql_Tables);
}
string tablename, key;
foreach (DataRow item in tables.Rows)
{
tablename = (string)item.ItemArray[0];
Dictionary <string, string> tableInfo = new Dictionary <string, string>();
tableInfo["Name"] = tablename;
if (item.ItemArray.Count() > 1)
{
tableInfo["Comment"] = (string)item.ItemArray[1];
}
else
{
tableInfo["Comment"] = (string)item.ItemArray[0];
}
key = tablename;//.ToLower();
result[key] = tableInfo;
}
return(result);
}
/// <summary>
/// 获取不同表里列名相同的列名
/// 不包括列名为ID,CommitTime,UpdateTime
/// 不包含列名为_ID
/// </summary>
/// <returns></returns>
public static string[] Same_Column_Name_In_TableName(string type = "tinyint")
{
string typeSql = "";
if (!string.IsNullOrEmpty(type))
{
typeSql = " DATA_TYPE='" + type + "' AND ";
}
string SQLStr = string.Format(Sql_Same_Column_Name, typeSql);
DataTable tables = UtilSqlserver.SqlExecute(SQLStr);
string[] Result = new string[tables.Rows.Count];
string Column_Name;
int i_index = 0;
foreach (DataRow item in tables.Rows)
{
Column_Name = (string)item.ItemArray[1];
if (Column_Name.ToUpper().Contains("_ID") || Column_Name.ToUpper().Equals("ID") || Column_Name.ToUpper().Equals("COMMITTIME") || Column_Name.ToUpper().Equals("UPDATETIME"))
{
}
else
{
Result.SetValue(Column_Name, i_index);
i_index += 1;
}
}
return(Result);
}
/// <summary>
/// 返回所有的数据库列表
/// </summary>
/// <returns></returns>
public static List <string> AllDatabaseNames()
{
List <string> result = new List <string>();
DataTable databases = UtilSqlserver.SqlExecute(Sql_Databases);
string database_name;
foreach (DataRow item in databases.Rows)
{
database_name = (string)item.ItemArray[0];
result.Add(database_name);
}
return(result);
}
/// <summary>
/// 查询所有表名
/// </summary>
/// <returns></returns>
public static Dictionary <string, string> TableList()
{
Dictionary <string, string> result = new Dictionary <string, string>();
DataTable tables = UtilSqlserver.SqlExecute(Sql_Tables);
string tablename, key;
foreach (DataRow item in tables.Rows)
{
tablename = (string)item.ItemArray[0];
key = tablename;//.ToLower();
if (!key.ToLower().Equals("sysdiagrams"))
{
result[key] = tablename;
}
}
return(result);
}
/// <summary>
/// 获取数据库所有的主键外键默认值
/// </summary>
/// <param name="table_name"></param>
/// <returns></returns>
public static Dictionary <string, Dictionary <string, string> > Table_FK_PK_DK(string table_name)
{
Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
string sql_fkpkdk = string.Format(Sql_PK_DK_FK, table_name);
DataTable tables = UtilSqlserver.SqlExecute(sql_fkpkdk);
Dictionary <string, string> fkpkdkInfo;
foreach (DataRow item in tables.Rows)
{
fkpkdkInfo = new Dictionary <string, string>();
///外键约束名，子表名，外键列名，父表名
fkpkdkInfo["constrant_type"] = (string)item.ItemArray[0];
fkpkdkInfo["constrant_name"] = (string)item.ItemArray[1];
fkpkdkInfo["constrant_keys"] = (string)item.ItemArray[6];
result[table_name] = fkpkdkInfo;
}
return(result);
}
/// <summary>
/// 查询数据库所有的外键
/// </summary>
/// <returns></returns>
public static Dictionary <string, Dictionary <string, string> > Table_Foreign_Key()
{
Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
DataTable tables = UtilSqlserver.SqlExecute(Sql_Table_Foreign_Key);
string tablename;
Dictionary <string, string> foreignKeyInfo;
foreach (DataRow item in tables.Rows)
{
tablename = (string)item.ItemArray[1];
foreignKeyInfo = new Dictionary <string, string>();
///外键约束名，子表名，外键列名，父表名
foreignKeyInfo["fkname"] = (string)item.ItemArray[0];
foreignKeyInfo["ftablename"] = (string)item.ItemArray[1];
foreignKeyInfo["fkcol"] = (string)item.ItemArray[2];
foreignKeyInfo["rtable"] = (string)item.ItemArray[3];
result[tablename] = foreignKeyInfo;
}
return(result);
}
/// <summary>
/// 获取指定表所有的列信息
/// </summary>
/// <param name="table_name"></param>
/// <returns></returns>
public static Dictionary <string, Dictionary <string, string> > FieldInfoList(string table_name)
{
Dictionary <string, Dictionary <string, string> > result = new Dictionary <string, Dictionary <string, string> >();
string sql = string.Format(Sql_Table_Columns, table_name);
DataTable columns = UtilSqlserver.SqlExecute(sql);
string column_name, column_type;
foreach (DataRow item in columns.Rows)
{
Dictionary <string, string> columnInfo = new Dictionary <string, string>();
column_name = (string)item.ItemArray[3];
columnInfo["Field"] = column_name;
column_type = (string)item.ItemArray[7];
columnInfo["Type"] = column_type;
var nullYN = (string)item.ItemArray[6];
if (nullYN.Contains("YES"))
{
nullYN = "是";
}
else
{
nullYN = "否";
}
columnInfo["Null"] = nullYN;
if (column_type.Contains("char"))
{
var len = item.ItemArray[8];
columnInfo["Length"] = len.ToString();
}
else if (column_type.Contains("numeric") || (column_type.Contains("int")))
{
var len = item.ItemArray[10];
columnInfo["Length"] = len.ToString();
}
else
{
columnInfo["Length"] = "";
}
var fkPk = "";
if (IsSqlserverDefault)
{
if (column_name.Equals("ID"))
{
fkPk = "PK";
}
if (column_name.Contains("_ID"))
{
fkPk = "FK";
}
}
else
{
string[] tbl = table_name.Split('_');
if (column_name.ToUpper().Contains("_ID") && (column_name.ToUpper().Contains(tbl[tbl.Length - 1].ToUpper())))
{
fkPk = "PK";
}
if (column_name.ToUpper().Contains("_ID") && (!column_name.ToUpper().Contains(tbl[tbl.Length - 1].ToUpper())))
{
fkPk = "FK";
}
}
columnInfo["Fkpk"] = fkPk;
sql = string.Format(Sql_Table_Columns_Comment, table_name, column_name);
DataTable column_comment = UtilSqlserver.SqlExecute(sql);
if ((column_comment == null) || (column_comment.Rows.Count == 0))
{
columnInfo["Comment"] = column_name;
Console.WriteLine("表：" + table_name + "-列:" + column_name + "【无注释】");
}
foreach (DataRow item_c in column_comment.Rows)
{
columnInfo["Comment"] = (string)item_c.ItemArray[1];
}
result[column_name] = columnInfo;
}
return(result);
}