public Dictionary <string, TableInfo> GetAllTableInfo(SqlDBUtil dbUtil, object[] queryInfo, string testModelTable = "")
{
// =======================================================
// 查詢 所有的欄位資訊
// =======================================================
// 兜組SQL
StringBuilder columnsQSql = new StringBuilder();
columnsQSql.Append("SELECT a.TABLE_NAME AS TABLE_NAME, \n");
columnsQSql.Append(" (SELECT Cast([value] AS VARCHAR(500)) \n");
columnsQSql.Append(" FROM Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, NULL, NULL)) AS TABLE_COMMENT, \n");
columnsQSql.Append(" b.COLUMN_NAME AS COLUMN_NAME, \n");
columnsQSql.Append(" b.DATA_TYPE AS DATA_TYPE, \n");
columnsQSql.Append(" b.CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH, \n");
columnsQSql.Append(" b.COLUMN_DEFAULT AS DATA_DEFAULT, \n");
columnsQSql.Append(" b.NUMERIC_PRECISION, \n");
columnsQSql.Append(" b.NUMERIC_SCALE, \n");
columnsQSql.Append(" b.IS_NULLABLE AS NULLABLE, \n");
columnsQSql.Append(" (SELECT Cast([value] AS VARCHAR(500)) \n");
columnsQSql.Append(" FROM Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, 'column', DEFAULT) \n");
columnsQSql.Append(" WHERE name = 'MS_Description' \n");
columnsQSql.Append(" AND objtype = 'COLUMN' \n");
columnsQSql.Append(" AND objname COLLATE Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME) AS COLUMN_COMMENT, \n");
columnsQSql.Append(" (SELECT Objectproperty(Object_id(c.CONSTRAINT_SCHEMA + '.' \n");
columnsQSql.Append(" + Quotename(c.CONSTRAINT_NAME)), 'IsPrimaryKey') \n");
columnsQSql.Append(" FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c \n");
columnsQSql.Append(" WHERE a.TABLE_NAME = c.TABLE_NAME \n");
columnsQSql.Append(" AND b.COLUMN_NAME = c.COLUMN_NAME \n");
columnsQSql.Append(" AND a.TABLE_CATALOG = b.TABLE_CATALOG \n");
columnsQSql.Append(" AND a.TABLE_SCHEMA = b.TABLE_SCHEMA) AS IS_PK \n");
columnsQSql.Append("FROM INFORMATION_SCHEMA.TABLES a \n");
columnsQSql.Append(" LEFT JOIN INFORMATION_SCHEMA.COLUMNS b \n");
columnsQSql.Append(" ON a.TABLE_NAME = b.TABLE_NAME \n");
columnsQSql.Append(" AND a.TABLE_CATALOG = b.TABLE_CATALOG \n");
columnsQSql.Append(" AND a.TABLE_SCHEMA = b.TABLE_SCHEMA \n");
columnsQSql.Append("WHERE TABLE_TYPE = 'BASE TABLE' \n");
columnsQSql.Append(" AND a.TABLE_CATALOG = '" + queryInfo[0] + "' \n");
columnsQSql.Append(" AND a.TABLE_SCHEMA = '" + queryInfo[1] + "' \n");
// 測試縮限範圍
if (StringUtil.NotEmpty(testModelTable))
{
columnsQSql.Append("AND a.TABLE_NAME = '" + testModelTable + "' ");
}
columnsQSql.Append("ORDER BY a.TABLE_NAME, \n");
columnsQSql.Append(" ordinal_position ");
// =======================================================
// 查詢
// =======================================================
// 查詢所有的欄位List
var allColumnList = dbUtil.Query(columnsQSql.ToString());
// =======================================================
// 資訊整理
// =======================================================
var tableDic = new Dictionary <string, TableInfo>();
foreach (var columnData in allColumnList)
{
//====================
//處理 table 資訊
//====================
var TABLE_NAME = StringUtil.SafeTrim(columnData["TABLE_NAME"]);
//取得 TABLE_INFO 容器
TableInfo tableInfo = null;
//不存在時新增
if (!tableDic.ContainsKey(TABLE_NAME))
{
//初始化
tableInfo = new TableInfo();
tableInfo.TABLE_NAME = TABLE_NAME;
tableInfo.TABLE_COMMENT = StringUtil.SafeTrim(columnData["TABLE_COMMENT"]);
tableInfo.ColumnInfoDic = new Dictionary <string, ColumnInfo>();
tableInfo.ColumnNameList = new List <string>();
//新增
tableDic[TABLE_NAME] = tableInfo;
}
else
{
tableInfo = tableDic[TABLE_NAME];
}
//====================
//處理 column 資訊
//====================
var COLUMN_NAME = StringUtil.SafeTrim(columnData["COLUMN_NAME"]);
//取得 COLUMN_INFO 容器
ColumnInfo columnInfo = null;
//不存在時新增
if (!tableInfo.ColumnInfoDic.ContainsKey(COLUMN_NAME))
{
//初始化
columnInfo = new ColumnInfo();
columnInfo.COLUMN_NAME = COLUMN_NAME;
columnInfo.COLUMN_COMMENT = StringUtil.SafeTrim(columnData["COLUMN_COMMENT"]);
columnInfo.DATA_TYPE = StringUtil.SafeTrim(columnData["DATA_TYPE"]);
columnInfo.DATA_LENGTH = int.Parse(StringUtil.SafeTrim(columnData["DATA_LENGTH"], "0"));
columnInfo.NUMBER_PRECISION = int.Parse(StringUtil.SafeTrim(columnData["NUMERIC_PRECISION"], "0"));
columnInfo.NUMBER_SCALE = int.Parse(StringUtil.SafeTrim(columnData["NUMERIC_SCALE"], "0"));
columnInfo.isNullable = "YES".Equals(StringUtil.SafeTrim(columnData["NULLABLE"]));
columnInfo.isPK = "1".Equals(StringUtil.SafeTrim(columnData["IS_PK"]));
//加入資料集
tableInfo.ColumnInfoDic[COLUMN_NAME] = columnInfo;
tableInfo.ColumnNameList.Add(COLUMN_NAME);
}
}
return(tableDic);
}
public Dictionary <string, SqlTableInfo> GetAllTable(SqlDBUtil dbUtil, string schema, string testModelTable = "")
{
// =======================================================
// 查詢 所有的欄位資訊
// =======================================================
// 兜組SQL
StringBuilder columnsQSql = new StringBuilder();
columnsQSql.Append("SELECT a.TABLE_NAME AS TABLE_NAME, \n");
columnsQSql.Append(" (SELECT Cast([value] AS VARCHAR(500)) \n");
columnsQSql.Append(" FROM Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, NULL, NULL)) AS TABLE_COMMENT, \n");
columnsQSql.Append(" b.COLUMN_NAME AS COLUMN_NAME, \n");
columnsQSql.Append(" b.DATA_TYPE AS DATA_TYPE, \n");
columnsQSql.Append(" b.CHARACTER_MAXIMUM_LENGTH AS DATA_LENGTH, \n");
columnsQSql.Append(" b.COLUMN_DEFAULT AS DATA_DEFAULT, \n");
columnsQSql.Append(" b.IS_NULLABLE AS NULLABLE, \n");
columnsQSql.Append(" (SELECT Cast([value] AS VARCHAR(500)) \n");
columnsQSql.Append(" FROM Fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, 'column', DEFAULT) \n");
columnsQSql.Append(" WHERE name = 'MS_Description' \n");
columnsQSql.Append(" AND objtype = 'COLUMN' \n");
columnsQSql.Append(" AND objname COLLATE Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME) AS COLUMN_COMMENT, \n");
columnsQSql.Append(" (SELECT Objectproperty(Object_id(c.CONSTRAINT_SCHEMA + '.' \n");
columnsQSql.Append(" + Quotename(c.CONSTRAINT_NAME)), 'IsPrimaryKey') \n");
columnsQSql.Append(" FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE c \n");
columnsQSql.Append(" WHERE a.TABLE_NAME = c.TABLE_NAME \n");
columnsQSql.Append(" AND b.COLUMN_NAME = c.COLUMN_NAME) AS IS_PK \n");
columnsQSql.Append("FROM INFORMATION_SCHEMA.TABLES a \n");
columnsQSql.Append(" LEFT JOIN INFORMATION_SCHEMA.COLUMNS b \n");
columnsQSql.Append(" ON ( a.TABLE_NAME = b.TABLE_NAME ) \n");
columnsQSql.Append("WHERE TABLE_TYPE = 'BASE TABLE' \n");
// 測試縮限範圍
if (StringUtil.NotEmpty(testModelTable))
{
columnsQSql.Append("AND a.TABLE_NAME = '" + testModelTable + "' ");
}
columnsQSql.Append("ORDER BY a.TABLE_NAME, \n");
columnsQSql.Append(" ordinal_position ");
// 查詢所有的欄位List
var allColumnList = dbUtil.Query(columnsQSql.ToString());
// 依據 table 分類
var tableColumnProcResult = Process(allColumnList);
// =======================================================
// 查詢 所有的欄位資訊
// =======================================================
var pKeyQSql = new StringBuilder();
pKeyQSql.Append("SELECT C.OWNER, ");
pKeyQSql.Append(" C.TABLE_NAME, ");
pKeyQSql.Append(" D.POSITION, ");
pKeyQSql.Append(" D.COLUMN_NAME ");
pKeyQSql.Append("FROM ALL_CONSTRAINTS C ");
pKeyQSql.Append(" JOIN ALL_CONS_COLUMNS D ");
pKeyQSql.Append(" ON C.OWNER = D.OWNER ");
pKeyQSql.Append(" AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME ");
pKeyQSql.Append("WHERE C.CONSTRAINT_TYPE = 'P' ");
pKeyQSql.Append(" AND C.OWNER = '"+ schema + "' ");
// 測試縮限範圍
if (StringUtil.NotEmpty(testModelTable))
{
columnsQSql.Append("AND C.TABLE_NAME = '" + testModelTable + "' ");
}
pKeyQSql.Append("ORDER BY C.TABLE_NAME, ");
pKeyQSql.Append(" D.POSITION ");
var allPKeyColumnList = dbUtil.Query(pKeyQSql.ToString());
// 依據 table 分類
var pkColumnProcResult = Process(allPKeyColumnList);
// =======================================================
// 組 tableInfo
// =======================================================
var tableInfoMap = new Dictionary <string, SqlTableInfo>();
foreach (var tableName in tableColumnProcResult.ColumnInfoListByTableName.Keys)
{
var tableInfo = new SqlTableInfo();
// 一般欄位
tableInfo.ColumnDataMapList = tableColumnProcResult.ColumnInfoListByTableName[tableName];
tableInfo.ColumnDataMapByColName = tableColumnProcResult.ColumnInfoByColNameTableName[tableName];
tableInfo.ColumnNameSet = tableColumnProcResult.ColumnNameSetByTableName[tableName];
// PK欄位
if (pkColumnProcResult.ColumnInfoListByTableName.ContainsKey(tableName))
{
tableInfo.PKeyDataMapList = pkColumnProcResult.ColumnInfoListByTableName[tableName];
tableInfo.PKeySet = pkColumnProcResult.ColumnNameSetByTableName[tableName];
}
else
{
tableInfo.PKeyDataMapList = new List <IDictionary <string, object> >();
tableInfo.PKeySet = new List <string>();
}
// PUT
tableInfoMap.Add(tableName, tableInfo);
}
return(tableInfoMap);
}
