示例#1
0
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection connection = MyDB.getConnection();
            SqlCommand    sqlCommand;
            SqlDataReader dataReader;
            string        output_definition = "";

            try
            {
                connection.Open();

                sqlCommand = new SqlCommand("DECLARE @PKRepetida varchar(40), @longitud int = 0, @i int = 0, @Nombre_columna varchar (40), @anomalias int, @j int = 0, @Id_objeto int, @EstaVacio int SET @longitud = (select count(*) from ( select COlUMN_NAME, COUNT(COLUMN_NAME) as duplicados from ( select TABLE_NAME,COLUMN_NAME, CONSTRAINT_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME NOT IN (select distinct TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where ORDINAL_POSITION > 1 ) )T1 where CONSTRAINT_NAME LIKE 'PK%' or CONSTRAINT_NAME LIKE 'UPKCL%' group by COLUMN_NAME)T2) WHILE @i < @longitud BEGIN SET @i = @i + 1 IF ((select duplicados from ( select COUNT(COLUMN_NAME) as duplicados,ROW_NUMBER() OVER (ORDER BY (select 1)) AS a from ( select TABLE_NAME,COLUMN_NAME, CONSTRAINT_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME NOT IN (select distinct TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where ORDINAL_POSITION > 1 ) )T1 where CONSTRAINT_NAME LIKE 'PK%' or CONSTRAINT_NAME LIKE 'UPKCL%' group by COLUMN_NAME)T2 where a = @i)>1) Begin SET @Nombre_columna =(select COLUMN_NAME from ( select COUNT(COLUMN_NAME) as duplicados, COLUMN_NAME,ROW_NUMBER() OVER (ORDER BY (select 1)) AS a from ( select TABLE_NAME,COLUMN_NAME, CONSTRAINT_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME NOT IN (select distinct TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where ORDINAL_POSITION > 1 ) )T1 where CONSTRAINT_NAME LIKE 'PK%' or CONSTRAINT_NAME LIKE 'UPKCL%' group by COLUMN_NAME)T2 where a = @i) print @Nombre_columna SET @anomalias = (select count(*) from ( select ROW_NUMBER() OVER (ORDER BY (select 1)) AS a, object_id from ( select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where COLUMN_NAME = @Nombre_columna)T1 join sys.objects so on (T1.TABLE_NAME = so.name) where CONSTRAINT_NAME LIKE 'PK%' or CONSTRAINT_NAME LIKE 'UPKCL%')T2) WHILE @j < @anomalias BEGIN SET @j = @j + 1 SET @Id_objeto = (select object_id from ( select ROW_NUMBER() OVER (ORDER BY (select 1)) AS a, object_id from ( select TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where COLUMN_NAME = @Nombre_columna) T1 join sys.objects so on (T1.TABLE_NAME = so.name) where CONSTRAINT_NAME LIKE 'PK%' or CONSTRAINT_NAME LIKE 'UPKCL%')T2 where a = @j) SET @EstaVacio = (select count(*) from sys.triggers st join sys.sql_modules ssm on (st.object_id = ssm.object_id ) where st.parent_id = @Id_objeto ) IF (@EstaVacio > 0) Begin select so.name as tabla,st.object_id, st.name , ssm.definition from sys.triggers st join sys.sql_modules ssm on (st.object_id = ssm.object_id ) join sys.objects so on (so.object_id = st.parent_id) where st.parent_id = @Id_objeto END END END END", connection);


                dataReader = sqlCommand.ExecuteReader();



                while (dataReader.Read())
                {
                    // Console.WriteLine("LEYENDO " + dataReader.FieldCount);
                    int index = 0;

                    string tabla      = dataReader.GetValue(index++).ToString();
                    long   object_id  = Int64.Parse(dataReader.GetValue(index++).ToString());
                    string name       = dataReader.GetValue(index++).ToString();
                    string definition = dataReader.GetValue(index++).ToString();


                    output_definition += "Tabla: " + tabla + ", Object_id: " + object_id + ", nombre: " + name + "\nDefinition: " + definition + "\n\n";
                }

                this.richTextBox2.Text = output_definition;
                MyFileManager.writeTXT(output_definition, "D://LOG_AUDITORIA_ANOMALIAS_DEFINICION.txt");



                sqlCommand.Dispose();
                dataReader.Close();
            }
            catch (Exception error)
            {
                Console.WriteLine("Error: " + error.Message);
            }
            finally
            {
                connection.Close();
            }
        }
示例#2
0
        private void buttonDetectAnomalies_Click(object sender, EventArgs e)
        {
            List <Anomaly> anomalies_dbcc      = AnomalyDetector.detectAnomaliesWithData();
            List <Anomaly> anomalies_structure = AnomalyDetector.detectAnomaliesWithNoData();


            string output_log = "";

            output_log += "**********ANOMALY in DATA***********\n";

            foreach (var item in anomalies_dbcc)
            {
                // string extra_summary = Trigger.getExtraSummary(item.object_id);

                output_log += "\n***Anomalía del objeto " + item.object_id + ": \n" + item.summary; // + "\n" + extra_summary + "\n";
            }

            output_log += "\n\n**********ANOMALY STRUCTURE*************\n";
            foreach (var item in anomalies_structure)
            {
                output_log += "\n***Anomalía structure in " + item.object_id + ": \n" + item.summary;
            }

            MyFileManager.writeTXT(output_log, "D://LOG_AUDITORIA_FULL_ANOMALIAS.txt");

            if (anomalies_dbcc.Count + anomalies_structure.Count > 0)
            {
                output_log += "\n\nLOG GENERADO CON ANOMALÍAS en D://AUDITORIA_LOG_.txt";
            }
            else
            {
                output_log += "\n\nNO SE DETECTARON ANOMALÍAS";
            }

            this.richTextBox1.Text = output_log;
        }
示例#3
0
        private void button_get_integrity_metadata_Click(object sender, EventArgs e)
        {
            SqlConnection connection = MyDB.getConnection();

            try
            {
                SqlCommand sqlCommand = new SqlCommand("SELECT LlaveForanea =o.name, Esquema =SCHEMA_NAME(t1.schema_id), Tabla =t1.name, Columna =c1.name, Esquema_Ref =SCHEMA_NAME(t2.schema_id), Tabla_Ref =t2.name, Columna_Ref =c2.name FROM sys.foreign_keys o INNER JOIN sys.foreign_key_columns fk ON o.object_id = fk.constraint_object_id INNER JOIN sys.tables t1 ON t1.object_id = fk.parent_object_id INNER JOIN sys.columns c1 ON c1.column_id = parent_column_id AND c1.object_id = t1.object_id INNER JOIN sys.tables t2 ON t2.object_id = fk.referenced_object_id INNER JOIN sys.columns c2 ON c2.column_id = referenced_column_id AND c2.object_id = t2.object_id", connection);

                connection.Open();

                // create data adapter
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlCommand);

                DataTable dtRecord = new DataTable();
                dataAdapter.Fill(dtRecord);
                dataGridView1.DataSource = dtRecord;


                sqlCommand = new SqlCommand("SELECT LlaveForanea =o.name, Esquema =SCHEMA_NAME(t1.schema_id), Tabla =t1.name, Columna =c1.name, Esquema_Ref =SCHEMA_NAME(t2.schema_id), Tabla_Ref =t2.name, Columna_Ref =c2.name FROM sys.foreign_keys o INNER JOIN sys.foreign_key_columns fk ON o.object_id = fk.constraint_object_id INNER JOIN sys.tables t1 ON t1.object_id = fk.parent_object_id INNER JOIN sys.columns c1 ON c1.column_id = parent_column_id AND c1.object_id = t1.object_id INNER JOIN sys.tables t2 ON t2.object_id = fk.referenced_object_id INNER JOIN sys.columns c2 ON c2.column_id = referenced_column_id AND c2.object_id = t2.object_id", connection);

                SqlDataReader dataReader = sqlCommand.ExecuteReader();
                string        output_log = "LlaveForanea,Esquema,Tabla,Columna,Esquema_Ref,Tabla_Ref,Columna_Ref\n";
                while (dataReader.Read())
                {
                    // Console.WriteLine("LEYENDO " + dataReader.FieldCount);
                    int index = 0;

                    string LlaveForanea = dataReader.GetValue(index++).ToString();
                    string Esquema      = dataReader.GetValue(index++).ToString();
                    string Tabla        = dataReader.GetValue(index++).ToString();
                    string Columna      = dataReader.GetValue(index++).ToString();
                    string Esquema_Ref  = dataReader.GetValue(index++).ToString();
                    string Tabla_Ref    = dataReader.GetValue(index++).ToString();
                    string Columna_Ref  = dataReader.GetValue(index++).ToString();

                    output_log += LlaveForanea + "," + Esquema + "," + Tabla + "," + Columna + "," + Esquema_Ref + "," + Tabla_Ref + "," + Columna_Ref + "\n";
                }


                MyFileManager.writeTXT(output_log, "D://LOG_AUDITORIA_RELACIONES_INTEGRIDAD_REFERENCIAL.txt");

                sqlCommand.Dispose();
                dataReader.Close();
            }
            catch (Exception error)
            {
                Console.WriteLine("Error: " + error.Message);
            }
            finally
            {
                connection.Close();
            }



            /*
             *
             * string[][] sqls = new string[][] {
             *  //--->Esquemas
             *  //--Sys.schemas
             *  new string[]{"sys-schemas", "[name],[schema_id],[principal_id]", "SELECT [name],[schema_id],[principal_id] FROM [pubs].[sys].[schemas]"},
             *  //--->Información general de tablas:
             *  //--INFORMATION_SCHEMA.TABLES
             *  new string[]{ "INFORMATION_SCHEMA-TABLES", "[TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME]", "SELECT [TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[TABLE_TYPE] FROM[pubs].[INFORMATION_SCHEMA].[TABLES]"},
             *  //--->Información de claves primarias y foráneas
             *  //--INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
             *  new string[]{ "INFORMATION_SCHEMA-REFERENTIAL_CONSTRAINTS", "[CONSTRAINT_CATALOG],[CONSTRAINT_SCHEMA],[CONSTRAINT_NAME],[UNIQUE_CONSTRAINT_CATALOG],[UNIQUE_CONSTRAINT_SCHEMA],[UNIQUE_CONSTRAINT_NAME],[MATCH_OPTION],[UPDATE_RULE],[DELETE_RULE]", "SELECT [CONSTRAINT_CATALOG],[CONSTRAINT_SCHEMA],[CONSTRAINT_NAME],[UNIQUE_CONSTRAINT_CATALOG],[UNIQUE_CONSTRAINT_SCHEMA],[UNIQUE_CONSTRAINT_NAME],[MATCH_OPTION],[UPDATE_RULE],[DELETE_RULE] FROM [pubs].[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]"},
             *  //--INFORMATION_SCHEMA.TABLE_CONSTRAINTS
             *  new string[]{ "INFORMATION_SCHEMA-TABLE_CONSTRAINTS", "[CONSTRAINT_CATALOG],[CONSTRAINT_SCHEMA],[CONSTRAINT_NAME],[TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[CONSTRAINT_TYPE],[IS_DEFERRABLE],[INITIALLY_DEFERRED]", "SELECT [CONSTRAINT_CATALOG],[CONSTRAINT_SCHEMA],[CONSTRAINT_NAME],[TABLE_CATALOG],[TABLE_SCHEMA],[TABLE_NAME],[CONSTRAINT_TYPE],[IS_DEFERRABLE],[INITIALLY_DEFERRED] FROM [pubs].[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]"},
             *  //--Sys.key_constraints
             *  new string[]{ "sys-key_constraints", "[name], [object_id], [principal_id],[schema_id],[parent_object_id],[type],[type_desc],[create_date],[modify_date],[is_ms_shipped],[is_published],[is_schema_published],[unique_index_id],[is_system_named],[is_enforced]", "SELECT [name], [object_id], [principal_id],[schema_id],[parent_object_id],[type],[type_desc],[create_date],[modify_date],[is_ms_shipped],[is_published],[is_schema_published],[unique_index_id],[is_system_named],[is_enforced] FROM [pubs].[sys].[key_constraints]"},
             *  //--Sys.foreign_key_columns
             *  new string[]{ "sys-foreign_key_columns", "[constraint_object_id],[constraint_column_id],[parent_object_id],[parent_column_id],[referenced_object_id],[referenced_column_id]", "SELECT [constraint_object_id],[constraint_column_id],[parent_object_id],[parent_column_id],[referenced_object_id],[referenced_column_id] FROM [pubs].[sys].[foreign_key_columns]"},
             *  //--Sys.foreign_key
             *  new string[]{ "sys-foreign_key", "[name],[object_id],[principal_id],[schema_id],[parent_object_id],[type],[type_desc],[create_date],[modify_date],[is_ms_shipped],[is_published],[is_schema_published],[referenced_object_id],[key_index_id],[is_disabled],[is_not_for_replication],[is_not_trusted],[delete_referential_action],[delete_referential_action_desc],[update_referential_action],[update_referential_action_desc],[is_system_named]", "SELECT [name],[object_id],[principal_id],[schema_id],[parent_object_id],[type],[type_desc],[create_date],[modify_date],[is_ms_shipped],[is_published],[is_schema_published],[referenced_object_id],[key_index_id],[is_disabled],[is_not_for_replication],[is_not_trusted],[delete_referential_action],[delete_referential_action_desc],[update_referential_action],[update_referential_action_desc],[is_system_named] FROM [pubs].[sys].[foreign_keys]"},
             *  //--->Información general detallada de las tablas
             *  //--Sys.tables
             *  new string[]{ "sys-tables", "[name] ,[object_id] ,[principal_id] ,[schema_id] ,[parent_object_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_published] ,[is_schema_published] ,[lob_data_space_id] ,[filestream_data_space_id] ,[max_column_id_used] ,[lock_on_bulk_load] ,[uses_ansi_nulls] ,[is_replicated] ,[has_replication_filter] ,[is_merge_published] ,[is_sync_tran_subscribed] ,[has_unchecked_assembly_data] ,[text_in_row_limit] ,[large_value_types_out_of_row] ,[is_tracked_by_cdc] ,[lock_escalation] ,[lock_escalation_desc] ,[is_filetable] ,[is_memory_optimized] ,[durability] ,[durability_desc] ,[temporal_type] ,[temporal_type_desc] ,[history_table_id] ,[is_remote_data_archive_enabled] ,[is_external] ,[history_retention_period] ,[history_retention_period_unit] ,[history_retention_period_unit_desc] ,[is_node] ,[is_edge]", "SELECT [name] ,[object_id] ,[principal_id] ,[schema_id] ,[parent_object_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_published] ,[is_schema_published] ,[lob_data_space_id] ,[filestream_data_space_id] ,[max_column_id_used] ,[lock_on_bulk_load] ,[uses_ansi_nulls] ,[is_replicated] ,[has_replication_filter] ,[is_merge_published] ,[is_sync_tran_subscribed] ,[has_unchecked_assembly_data] ,[text_in_row_limit] ,[large_value_types_out_of_row] ,[is_tracked_by_cdc] ,[lock_escalation] ,[lock_escalation_desc] ,[is_filetable] ,[is_memory_optimized] ,[durability] ,[durability_desc] ,[temporal_type] ,[temporal_type_desc] ,[history_table_id] ,[is_remote_data_archive_enabled] ,[is_external] ,[history_retention_period] ,[history_retention_period_unit] ,[history_retention_period_unit_desc] ,[is_node] ,[is_edge] FROM [pubs].[sys].[tables]"},
             *  //--Sys.columns
             *  new string[]{ "sys-columns", "[object_id] ,[name] ,[column_id] ,[system_type_id] ,[user_type_id] ,[max_length] ,[precision] ,[scale] ,[collation_name] ,[is_nullable] ,[is_ansi_padded] ,[is_rowguidcol] ,[is_identity] ,[is_computed] ,[is_filestream] ,[is_replicated] ,[is_non_sql_subscribed] ,[is_merge_published] ,[is_dts_replicated] ,[is_xml_document] ,[xml_collection_id] ,[default_object_id] ,[rule_object_id] ,[is_sparse] ,[is_column_set] ,[generated_always_type] ,[generated_always_type_desc] ,[encryption_type] ,[encryption_type_desc] ,[encryption_algorithm_name] ,[column_encryption_key_id] ,[column_encryption_key_database_name] ,[is_hidden] ,[is_masked] ,[graph_type] ,[graph_type_desc]", "SELECT [object_id] ,[name] ,[column_id] ,[system_type_id] ,[user_type_id] ,[max_length] ,[precision] ,[scale] ,[collation_name] ,[is_nullable] ,[is_ansi_padded] ,[is_rowguidcol] ,[is_identity] ,[is_computed] ,[is_filestream] ,[is_replicated] ,[is_non_sql_subscribed] ,[is_merge_published] ,[is_dts_replicated] ,[is_xml_document] ,[xml_collection_id] ,[default_object_id] ,[rule_object_id] ,[is_sparse] ,[is_column_set] ,[generated_always_type] ,[generated_always_type_desc] ,[encryption_type] ,[encryption_type_desc] ,[encryption_algorithm_name] ,[column_encryption_key_id] ,[column_encryption_key_database_name] ,[is_hidden] ,[is_masked] ,[graph_type] ,[graph_type_desc] FROM [pubs].[sys].[columns]"},
             *  //--Sys.systypes
             *  new string[]{ "sys-systypes", "[name] ,[xtype] ,[status] ,[xusertype] ,[length] ,[xprec] ,[xscale] ,[tdefault] ,[domain] ,[uid] ,[reserved] ,[collationid] ,[usertype] ,[variable] ,[allownulls] ,[type] ,[printfmt] ,[prec] ,[scale] ,[collation]", "SELECT [name] ,[xtype] ,[status] ,[xusertype] ,[length] ,[xprec] ,[xscale] ,[tdefault] ,[domain] ,[uid] ,[reserved] ,[collationid] ,[usertype] ,[variable] ,[allownulls] ,[type] ,[printfmt] ,[prec] ,[scale] ,[collation] FROM [pubs].[sys].[systypes]"},
             *  //--Sys.default_constraints
             *  new string[]{ "sys-default_constraints", "[name] ,[object_id] ,[principal_id] ,[schema_id] ,[parent_object_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_published] ,[is_schema_published] ,[parent_column_id] ,[definition] ,[is_system_named]", "SELECT [name] ,[object_id] ,[principal_id] ,[schema_id] ,[parent_object_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_published] ,[is_schema_published] ,[parent_column_id] ,[definition] ,[is_system_named] FROM [pubs].[sys].[default_constraints]"},
             *  //--->Restricciones / Checks
             *  //--INFORMATION_SCHEMA.CHECK_CONSTRAINTS
             *  new string[]{ "INFORMATION_SCHEMA-CHECK_CONSTRAINTS", "[CONSTRAINT_CATALOG] ,[CONSTRAINT_SCHEMA] ,[CONSTRAINT_NAME] ,[CHECK_CLAUSE]", "SELECT [CONSTRAINT_CATALOG] ,[CONSTRAINT_SCHEMA] ,[CONSTRAINT_NAME] ,[CHECK_CLAUSE] FROM [pubs].[INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]"},
             *  //--Sys.check_constraints
             *  new string[]{ "sys-check_constraints", "[name] ,[object_id] ,[principal_id] ,[schema_id] ,[parent_object_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_published] ,[is_schema_published] ,[is_disabled] ,[is_not_for_replication] ,[is_not_trusted] ,[parent_column_id] ,[definition] ,[uses_database_collation] ,[is_system_named]", "SELECT [name] ,[object_id] ,[principal_id] ,[schema_id] ,[parent_object_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_published] ,[is_schema_published] ,[is_disabled] ,[is_not_for_replication] ,[is_not_trusted] ,[parent_column_id] ,[definition] ,[uses_database_collation] ,[is_system_named] FROM [pubs].[sys].[check_constraints]"},
             *  //--Sys.triggers
             *  // new string[]{"sys-triggers", "[name] ,[object_id] ,[parent_class] ,[parent_class_desc] ,[parent_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_disabled] ,[is_not_for_replication] ,[is_instead_of_trigger]", "SELECT [name] ,[object_id] ,[parent_class] ,[parent_class_desc] ,[parent_id] ,[type] ,[type_desc] ,[create_date] ,[modify_date] ,[is_ms_shipped] ,[is_disabled] ,[is_not_for_replication] ,[is_instead_of_trigger] FROM [pubs].[sys].[triggers]"},
             *
             *  };
             *
             *
             * for (int i = 0; i < sqls.Length; i++)
             * {
             *  string filename = sqls[i][0], header = sqls[i][1], sql = sqls[i][2];
             *  MyDB.selectAllAndWrite(sql, "D://" + filename + ".txt", header);
             * }
             *
             * MessageBox.Show("TXT generados con los metadatos en D://");
             */
        }