public override TableName[] GetTableNames(DatabaseName dname)
{
if (dname.Provider.Version >= 2005)
{
var table = dname.FillDataTable($"USE [{dname.Name}] ; SELECT SCHEMA_NAME(schema_id) AS SchemaName, name as TableName FROM sys.Tables ORDER BY SchemaName,Name");
if (table != null)
{
return(table
.AsEnumerable()
.Select(row => new TableName(dname, row.Field <string>("SchemaName"), row.Field <string>("TableName")))
.ToArray());
}
}
else
{
var table = dname.FillDataTable($"USE [{dname.Name}] ; EXEC sp_tables");
if (table != null)
{
return(table
.AsEnumerable()
.Where(row => row.Field <string>("TABLE_TYPE") == "TABLE")
.Select(row => new TableName(dname, row.Field <string>("TABLE_OWNER"), row.Field <string>("TABLE_NAME")))
.ToArray());
}
}
return(new TableName[] { });
}
public override TableName[] GetProcedureNames(DatabaseName dname)
{
var table = dname.FillDataTable($"USE [{dname.Name}]; SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES");
var L = table.AsEnumerable().Select(row => new
{
type = row.Field <string>("ROUTINE_TYPE"),
schema = row.Field <string>("ROUTINE_SCHEMA"),
name = row.Field <string>("ROUTINE_NAME")
})
.Where(row => !(row.name.StartsWith("sp_") || row.name.StartsWith("fn_"))) //System Procedure or Function
.OrderByDescending(row => row.type) // Sort: Procedure, Function
.ThenBy(row => row.name)
.ToList();
List <TableName> list = new List <TableName>();
foreach (var row in L)
{
TableNameType type = TableNameType.Table;
switch (row.type)
{
case "PROCEDURE":
type = TableNameType.Procedure;
break;
case "FUNCTION":
type = TableNameType.Function;
break;
}
TableName tname = new TableName(dname, row.schema, row.name)
{
Type = type
};
list.Add(tname);
}
return(list.ToArray());
}
public override TableName[] GetProcedureNames(DatabaseName dname)
{
var table = dname.FillDataTable($"USE [{dname.Name}]; SELECT ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES");
List <TableName> list = new List <TableName>();
foreach (DataRow row in table.Rows)
{
string type = row.Field <string>("ROUTINE_TYPE");
string schema = row.Field <string>("ROUTINE_SCHEMA");
string name = row.Field <string>("ROUTINE_NAME");
TableNameType _type = TableNameType.Table;
//System Procedure or Function
if (name.StartsWith("sp_") || name.StartsWith("fn_"))
{
continue;
}
switch (type)
{
case "PROCEDURE":
_type = TableNameType.Procedure;
break;
case "FUNCTION":
_type = TableNameType.Function;
break;
}
TableName tname = new TableName(dname, schema, name)
{
Type = _type
};
list.Add(tname);
}
return(list.ToArray());
}