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); }