private static string GetSQL(DatabaseInfo.VersionTypeEnum version, SqlOption options) { string sql = ""; sql += "SELECT T.object_id, O.type AS ObjectType, ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, T.type, CAST(ISNULL(tei.object_id,0) AS bit) AS IsInsert, CAST(ISNULL(teu.object_id,0) AS bit) AS IsUpdate, CAST(ISNULL(ted.object_id,0) AS bit) AS IsDelete, T.parent_id, S.name AS Owner,T.name,is_disabled,is_not_for_replication,is_instead_of_trigger "; sql += "FROM sys.triggers T "; sql += "INNER JOIN sys.objects O ON O.object_id = T.parent_id "; sql += "INNER JOIN sys.schemas S ON S.schema_id = O.schema_id "; sql += "LEFT JOIN sys.trigger_events AS tei ON tei.object_id = T.object_id and tei.type=1 "; sql += "LEFT JOIN sys.trigger_events AS teu ON teu.object_id = T.object_id and teu.type=2 "; sql += "LEFT JOIN sys.trigger_events AS ted ON ted.object_id = T.object_id and ted.type=3 "; if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) { sql += ",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,"; sql += "(SELECT null AS name) AS AF"; } else { sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = T.object_id "; sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id"; } sql += " ORDER BY T.parent_id"; return sql; }
private static string GetSQL(SqlOption options) { string filter = ""; string sql = ""; sql += "SELECT O.name, O.type, M.object_id, OBJECT_DEFINITION(M.object_id) AS Text FROM sys.sql_modules M "; sql += "INNER JOIN sys.objects O ON O.object_id = M.object_id "; sql += "WHERE "; if (options.Ignore.FilterStoreProcedure) filter += "O.type = 'P' OR "; if (options.Ignore.FilterView) filter += "O.type = 'V' OR "; if (options.Ignore.FilterTrigger) filter += "O.type = 'TR' OR "; if (options.Ignore.FilterFunction) filter += "O.type IN ('IF','FN','TF') OR "; filter = filter.Substring(0, filter.Length - 4); return sql + filter; }
/// <summary> /// Constructor de la clase. /// </summary> /// <param name="connectioString">Connection string de la base</param> public GenerateDatabase(string connectioString, SqlOption filter) { this.connectioString = connectioString; this.objectFilter = filter; }