public override TableName[] GetTableNames(DatabaseName dname)
{
var table = new SqlCmd(dname.Provider, $"SELECT TABLE_SCHEMA AS SchemaName, TABLE_NAME as TableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' ORDER BY TABLE_SCHEMA,TABLE_NAME")
.FillDataTable();
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row["SchemaName"].IsNull(string.Empty), row.Field <string>("TableName")))
.ToArray());
}
return(new TableName[] { });
}
public override TableName[] GetTableNames(DatabaseName dname)
{
var table = new SqlCmd(dname.Provider, $"SELECT NULL AS SchemaName, NAME as TableName FROM sqlite_master WHERE TYPE='table' AND NOT (name LIKE 'sqlite_%') ORDER BY NAME")
.FillDataTable();
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row["SchemaName"].IsNull(string.Empty), row.Field <string>("TableName")))
.ToArray());
}
return(new TableName[] { });
}
public override DependencyInfo[] GetDependencySchema(DatabaseName dname)
{
const string sql = @"
SELECT
FK.TABLE_SCHEMA AS FK_SCHEMA,
FK.TABLE_NAME AS FK_Table,
PK.TABLE_SCHEMA AS PK_SCHEMA,
PK.TABLE_NAME AS PK_Table,
PT.COLUMN_NAME AS PK_Column,
CU.COLUMN_NAME AS FK_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN ( SELECT i1.TABLE_NAME ,
i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE FK.TABLE_NAME <> PK.TABLE_NAME
";
var dt = new SqlCmd(dname.Provider, sql).FillDataTable();
DependencyInfo[] rows = dt.AsEnumerable().Select(
row => new DependencyInfo
{
FkTable = new TableName(dname, row["FK_SCHEMA"].IsNull(string.Empty), (string)row["FK_Table"]),
PkTable = new TableName(dname, row["PK_SCHEMA"].IsNull(string.Empty), (string)row["PK_Table"]),
PkColumn = (string)row["PK_Column"],
FkColumn = (string)row["FK_Column"]
})
.ToArray();
return(rows);
}
