/// <summary>
/// カラム定義を取得する
/// </summary>
/// <param name="tableName">カラム定義を取得したいテーブルの名称</param>
/// <returns></returns>
public List<ColumnDefinition> GetDefinitions(string tableName)
{
List<ColumnDefinition> definitions = new List<ColumnDefinition> { };
// SQL文を生成する
StringBuilder query = new StringBuilder();
query.Append("SELECT c.name AS COLUMN_NAME,tp.name AS COLUMN_TYPE, c.max_length AS COLUMN_MAX_LENGTH, c.max_length AS COLUMN_MAX_LENGTH, c.is_identity AS IS_IDENTITY, c.is_nullable AS IS_NULLABLE");
query.Append(" FROM sys.tables t, sys.columns c, sys.types tp");
query.Append(" WHERE t.name = @table_name AND t.object_id = c.object_id AND c.user_type_id = tp.user_type_id");
// DBと接続
using (SqlCommand cmd = new SqlCommand(query.ToString(), this.SqlConnection))
{
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@table_name";
param.SqlDbType = SqlDbType.Char;
param.Direction = ParameterDirection.Input;
param.Value = tableName;
cmd.Parameters.Add(param);
// SQL文の実行
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int index = 0;
ColumnDefinition definition = new ColumnDefinition();
if (!reader.IsDBNull(index))
{
definition.ColumnName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.TypeName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Length = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.IsIdentity = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.IsNullableBool = reader.GetBoolean(index);
}
index++;
if (definition.ColumnName.Length > 0)
{
definitions.Add(definition);
}
}
}
}
return definitions;
}
/// <summary>
/// カラム定義を取得する
/// </summary>
/// <param name="tableName">カラム定義を取得したいテーブルの名称</param>
/// <returns></returns>
private List<ColumnDefinition> _GetDefinitions(string tableName)
{
List<ColumnDefinition> definitions = new List<ColumnDefinition> { };
// SQL文を生成する
StringBuilder query = new StringBuilder();
query.Append("EXEC sp_columns @table_name");
// DBと接続
using (SqlCommand cmd = new SqlCommand(query.ToString(), this.SqlConnection))
{
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@table_name";
param.SqlDbType = SqlDbType.Char;
param.Direction = ParameterDirection.Input;
param.Value = tableName;
cmd.Parameters.Add(param);
// SQL文の実行
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int index = 0;
ColumnDefinition definition = new ColumnDefinition();
if (!reader.IsDBNull(index))
{
definition.TableQualifier = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.TableOwner = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.TableName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.ColumnName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.DataType = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.TypeName = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Precision = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Length = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Scale = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Radix = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Nullable = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.Remarks = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.ColumnDef = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.SqlDataType = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.SqlDateTimeSub = reader.GetInt16(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.CharOctetLength = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.OriginalPosition = reader.GetInt32(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.IsNullable = reader.GetString(index);
}
index++;
if (!reader.IsDBNull(index))
{
definition.SsDataType = reader.GetByte(index);
}
index++;
if (definition.TableName.Length > 0)
{
definitions.Add(definition);
}
}
}
}
return definitions;
}
