protected virtual IList<TableDefinition> ReadTables()
{
const string query = @"SELECT OBJECT_SCHEMA_NAME(t.[object_id],DB_ID()) AS [Schema], t.name AS [Table],
c.[Name] AS ColumnName,
t.object_id AS [TableID],
c.column_id AS [ColumnID],
def.definition AS [DefaultValue],
c.[system_type_id] AS [TypeID],
c.[user_type_id] AS [UserTypeID],
c.[max_length] AS [Length],
c.[precision] AS [Precision],
c.[scale] AS [Scale],
c.[is_identity] AS [IsIdentity],
c.[is_nullable] AS [IsNullable],
CASE WHEN EXISTS(SELECT 1 FROM sys.foreign_key_columns fkc WHERE t.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id) THEN 1 ELSE 0 END AS IsForeignKey,
CASE WHEN EXISTS(select 1 from sys.index_columns ic WHERE t.object_id = ic.object_id AND c.column_id = ic.column_id) THEN 1 ELSE 0 END AS IsIndexed
,CASE WHEN kcu.CONSTRAINT_NAME IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey
, CASE WHEN EXISTS(select stc.CONSTRAINT_NAME, skcu.TABLE_NAME, skcu.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS stc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE skcu ON skcu.CONSTRAINT_NAME = stc.CONSTRAINT_NAME WHERE stc.CONSTRAINT_TYPE = 'UNIQUE'
AND skcu.TABLE_NAME = t.name AND skcu.COLUMN_NAME = c.name) THEN 1 ELSE 0 END AS IsUnique
,pk.name AS PrimaryKeyName
FROM sys.all_columns c
JOIN sys.tables t ON c.object_id = t.object_id AND t.type = 'u'
LEFT JOIN sys.default_constraints def ON c.default_object_id = def.object_id
LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND pk.type = 'PK'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON t.name = kcu.TABLE_NAME AND c.name = kcu.COLUMN_NAME AND pk.name = kcu.CONSTRAINT_NAME
ORDER BY t.name, c.name";
DataSet ds = Read(query);
DataTable dt = ds.Tables[0];
IList<TableDefinition> tables = new List<TableDefinition>();
foreach (DataRow dr in dt.Rows)
{
List<TableDefinition> matches = (from t in tables
where t.Name == dr["Table"].ToString()
&& t.SchemaName == dr["Schema"].ToString()
select t).ToList();
TableDefinition tableDef = null;
if (matches.Count > 0) tableDef = matches[0];
// create the table if not found
if (tableDef == null)
{
tableDef = new TableDefinition
{
Name = dr["Table"].ToString(),
SchemaName = dr["Schema"].ToString()
};
tables.Add(tableDef);
}
//find the column
List<ColumnDefinition> cmatches = (from c in tableDef.Columns
where c.Name == dr["ColumnName"].ToString()
select c).ToList();
ColumnDefinition colDef = null;
if (cmatches.Count > 0) colDef = cmatches[0];
if (colDef == null)
{
//need to create and add the column
tableDef.Columns.Add(new ColumnDefinition
{
Name = dr["ColumnName"].ToString(),
CustomType = "", //TODO: set this property
DefaultValue = dr.IsNull("DefaultValue") ? "" : dr["DefaultValue"].ToString(),
IsForeignKey = dr["IsForeignKey"].ToString() == "1",
IsIdentity = dr["IsIdentity"].ToString() == "True",
IsIndexed = dr["IsIndexed"].ToString() == "1",
IsNullable = dr["IsNullable"].ToString() == "True",
IsPrimaryKey = dr["IsPrimaryKey"].ToString() == "1",
IsUnique = dr["IsUnique"].ToString() == "1",
Precision = int.Parse(dr["Precision"].ToString()),
PrimaryKeyName = dr.IsNull("PrimaryKeyName") ? "" : dr["PrimaryKeyName"].ToString(),
Size = int.Parse(dr["Length"].ToString()),
TableName = dr["Table"].ToString(),
Type = GetDbType(int.Parse(dr["TypeID"].ToString())), //TODO: set this property
ModificationType = ColumnModificationType.Create
});
}
}
return tables;
}
