public IList<View> GetViews(Schema schema)
{
IList<View> viewList = this.getViewsForSchema(schema);
if (viewList != null && viewList.Count > 0)
{
var columnInspector = new ColumnInspector(this.peta);
var indexInspector = new IndexInspector(this.peta);
for (int v = 0; v < viewList.Count; v++)
{
var view = viewList[v];
view.Columns = columnInspector.GetColumns(view);
view.Indexes = indexInspector.GetIndexes(view);
view.Parent = schema;
}
}
return viewList;
}
private IList<ScalarFunction> queryForScalarFunctions(Schema schema)
{
var sql = new Sql(@"SELECT
J.[name] AS FunctionName
, J.object_id AS FunctionId
, EP.value AS [Description]
, R.DATA_TYPE AS ReturnDataType
, R.CHARACTER_MAXIMUM_LENGTH AS ReturnTypeMaximumLength
, CONVERT(INT, R.NUMERIC_PRECISION) AS ReturnTypePrecision
, CONVERT(INT, R.NUMERIC_SCALE) AS ReturnTypeScale
FROM sys.objects AS J
INNER JOIN sys.schemas AS S
ON ( J.schema_id = S.schema_id )
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS R
ON ( R.SPECIFIC_SCHEMA = S.[name] AND R.SPECIFIC_NAME = J.[name] )
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = J.object_id AND EP.minor_id = 0 )
WHERE J.schema_id = @0
AND J.[type] = 'FN'
ORDER BY J.[name];", schema.SchemaId);
return this.peta.Fetch<ScalarFunction>(sql);
}
public IList<TableFunction> GetTableFunctions(Schema schema)
{
IList<TableFunction> tfList = this.queryForTableFunctions(schema);
if (tfList != null && tfList.Count > 0)
{
var paramInspector = new ParameterInspector(this.peta);
var columnInspector = new ColumnInspector(this.peta);
for (int t = 0; t < tfList.Count; t++)
{
var func = tfList[t];
func.Parameters = paramInspector.GetParameters(func);
func.Columns = columnInspector.GetColumns(func);
func.Parent = schema;
}
}
return tfList;
}
public IList<Table> GetTables(Schema schema)
{
var tableList = this.queryForTables(schema);
if (tableList != null && tableList.Count > 0)
{
Table table = null;
var columnInspector = new ColumnInspector(this.peta);
var indexInspector = new IndexInspector(this.peta);
var foreignKeyInspector = new ForeignKeyInspector(this.peta);
for (int i = 0; i < tableList.Count; i++)
{
table = tableList[i];
table.Columns = columnInspector.GetColumns(table);
table.Indexes = indexInspector.GetIndexes(table);
table.ForeignKeys = foreignKeyInspector.GetForeignKeys(table);
table.Parent = schema;
}
}
return tableList;
}
private Database getTestDatabase()
{
var db = new Database { DatabaseName = "TEST", Description = "OLTP for tests", ObjectId = 0, Parent = null, Schemas = null };
var schema = new Schema { SchemaId = 1, SchemaName = "Sales", Description = "Sales department", Parent = db };
db.Schemas = new List<Schema>();
db.Schemas.Add(schema);
var view = new View { ViewId = 20, ViewName = "vTest", Description = "Sample view", Parent = schema };
schema.Views = new List<View>();
schema.Views.Add(view);
return db;
}
private IList<Table> queryForTables(Schema schema)
{
var sql = new Sql(@"SELECT T.name AS TableName
, COALESCE(EP.value, '') AS [Description]
, T.object_id AS TableId
FROM sys.tables AS T
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.major_id = T.object_id AND EP.minor_id = 0 AND EP.name = 'MS_Description' )
WHERE T.schema_id = @0
ORDER BY T.name", schema.SchemaId);
return this.peta.Fetch<Table>(sql);
}
private IList<View> getViewsForSchema(Schema schema)
{
var sql = new Sql(@"SELECT
V.object_id AS ViewId
, V.[name] AS ViewName
, EP.value AS [Description]
FROM sys.views AS V
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( V.object_id = EP.major_id AND EP.class = 1 AND EP.minor_id = 0 AND EP.[name] = 'MS_Description' )
WHERE schema_id = @0
ORDER BY V.[name];", schema.SchemaId);
return this.peta.Fetch<View>(sql);
}
private IList<StoredProcedure> queryForStoredProcedures(Schema schema)
{
var sql = new Sql(@"SELECT
SP.[name] AS ProcedureName
, SP.object_id AS ProcedureId
, EP.value AS [Description]
FROM sys.procedures AS SP
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = SP.object_id AND EP.minor_id = 0 )
WHERE SP.schema_id = @0
ORDER BY SP.[name];", schema.SchemaId);
return this.peta.Fetch<StoredProcedure>(sql);
}
public IList<StoredProcedure> GetStoredProcedures(Schema schema)
{
IList<StoredProcedure> spList = null;
spList = this.queryForStoredProcedures(schema);
if (spList != null && spList.Count > 0)
{
var parameterInspector = new ParameterInspector(this.peta);
for (int p = 0; p < spList.Count; p++)
{
var proc = spList[p];
proc.Parameters = parameterInspector.GetParameters(proc);
proc.Parent = schema;
}
}
return spList;
}
public IList<ScalarFunction> GetScalarFunctions(Schema schema)
{
IList<ScalarFunction> functionList = null;
functionList = this.queryForScalarFunctions(schema);
if (functionList != null && functionList.Count > 0)
{
var paramInspector = new ParameterInspector(this.peta);
for (int f = 0; f < functionList.Count; f++)
{
var sf = functionList[f];
sf.Parameters = paramInspector.GetParameters(sf);
sf.Parent = schema;
}
}
return functionList;
}
private IList<TableFunction> queryForTableFunctions(Schema schema)
{
var sql = new Sql(@"SELECT
J.[name] AS FunctionName
, J.object_id AS FunctionId
, EP.value AS [Description]
FROM sys.objects AS J
INNER JOIN sys.schemas AS S
ON ( J.schema_id = S.schema_id )
INNER JOIN INFORMATION_SCHEMA.ROUTINES AS R
ON ( R.SPECIFIC_SCHEMA = S.[name] AND R.SPECIFIC_NAME = J.[name] )
LEFT OUTER JOIN sys.extended_properties AS EP
ON ( EP.class = 1 AND EP.name = 'MS_Description' AND EP.major_id = J.object_id AND EP.minor_id = 0 )
WHERE J.schema_id = @0
AND J.[type] IN ( 'TF', 'IF' )
ORDER BY J.[name];", schema.SchemaId);
return this.peta.Fetch<TableFunction>(sql);
}
