Table Profiling Stored Procedure

Below Stored Procedure is made for profiling the individual table. Just run the Stored procedure with any table name, and after run display the table "Data_profil_results". it will give out all the columns level details like, total count for that particular comun [based on NULL and Not NULL], their distinct values, their constraint type and name.

Table Structure for "Data_profil_results" Table


CREATE

TABLE DATA_PROFIL_RESULTS ( TABLE_NAME
VARCHAR2(100), COLUMN_NAME
VARCHAR2(100), DATA_TYPE
VARCHAR2(100), DATA_LENGTH
VARCHAR2(100), DISTINCT_VALUES
VARCHAR2(100), MAX_VALUE
VARCHAR2(100), MIN_VALUE
VARCHAR2(100),COUNT_REC_NOT_NULL
VARCHAR2(100), COUNT_REC_NULL
VARCHAR2(100), NULLABLE
VARCHAR2(10), CONSTRAINT_TYPE
VARCHAR2(100), CONSTRAINT_NAME
VARCHAR2(100),TOTAL_REC_COUNT
VARCHAR2(100),NULL_REC_PERCN
VARCHAR2(100),)



-------------------------create or replace procedure SP_Data_Profiling_Results1(Arg_Table_Name IN VARCHAR2)
AsTable_Name1 USER_TAB_COLUMNS.table_name%type;Table_Name2 USER_TAB_COLUMNS.table_name%
type; Column_Name
varchar2(100);Data_Type
varchar2(100);Data_Length
varchar2(100);NULLABLE1
varchar2(10);constraint_type1
varchar2(100);constraint_name1
varchar2(100);Distinct_Values
NUMBER(10); No_NULL
NUMBER(10);Yes_NULL
NUMBER(10);Total_Rec_Count
NUMBER(10);Null_Rec_Percen
decimal(5,2);Max_value
varchar2(100);Min_value
varchar2(100);stat1
varchar(1000);CURSOR ALL_COLUMN(tablename varchar2) isSELECT
COLUMN_NAME ,
DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM USER_TAB_COLUMNS WHERE TABLE_NAME=tablename ;CURSOR ALL_COLUMN_CONS(tablename varchar2) isSELECT
CT.constraint_type,
UC.constraint_name,
UC.COLUMN_NAME
from user_cons_columns UC, user_constraints CTWHERE UC.TABLE_NAME=CT.TABLE_NAME
and UC.TABLE_NAME=tablename;
　
all_col_profile all_column%
rowtype;all_col_cons_profile ALL_COLUMN_CONS%
rowtype;　

BeginTable_Name1 := Arg_Table_Name;OPEN ALL_COLUMN(table_name1) ;OPEN ALL_COLUMN_CONS(table_name1) ;execute IMMEDIATE ' select count(*) ' ||' FROM '|| Table_Name1 INTO Total_Rec_Count;LOOP
FETCH ALL_COLUMN_CONS INTO all_col_cons_profile;FETCH ALL_COLUMN INTO all_col_profile;
EXIT WHEN ALL_COLUMN%NOTFOUND;


IF(TRIM(all_col_cons_profile.Column_Name) = TRIM(all_col_profile.Column_Name))
THEN constraint_type1 := all_col_cons_profile.constraint_type;
constraint_name1 := all_col_cons_profile.constraint_name;

ELSEconstraint_type1 :='';constraint_name1 :=
'';
END IF;

Column_Name := all_col_profile.COLUMN_NAME;
Data_Type := all_col_profile.DATA_TYPE;
Data_Length := all_col_profile.DATA_LENGTH;
NULLABLE1 := all_col_profile.NULLABLE;

　
execute IMMEDIATE ' select count(distinct '|| Column_Name||' ) ' ||' FROM '|| Table_Name1 INTO Distinct_Values;execute IMMEDIATE ' select count('|| Column_Name||' ) ' ||' FROM '|| Table_Name1 || ' where ' || Column_Name || ' IS NOT NULL ' INTO No_NULL;execute IMMEDIATE ' select count(*) ' ||' FROM '|| Table_Name1 || ' WHERE ' || Column_Name || ' IS NULL ' INTO Yes_NULL;execute IMMEDIATE ' select MAX( ' || Column_Name || ' ) ' ||' FROM '|| Table_Name1 INTO Max_value;execute IMMEDIATE ' select MIN( ' || Column_Name || ' ) ' ||' FROM '|| Table_Name1 INTO Min_value;Null_Rec_Percen := (Yes_NULL/Total_Rec_Count)*
100;


INSERT INTO Data_profil_results ( Table_Name,
Column_Name ,
Data_Type,
Data_Length ,
Distinct_Values ,
Max_value ,
Min_value ,
Count_Rec_Not_NULL ,
Count_Rec_NULL,
NULLABLE,
constraint_type,
constraint_name,
Total_Rec_Count,
Null_Rec_Percen
)

Values(
Table_Name1,
Column_Name,
Data_Type,
Data_Length,
Distinct_Values,
Max_value ,
Min_value ,
No_NULL,
Yes_NULL,
NULLABLE1,
constraint_type1,
constraint_name1,
Total_Rec_Count,
Null_Rec_Percen
);
COMMIT;　
END LOOP;CLOSE ALL_COLUMN;CLOSE ALL_COLUMN_CONS; END;
　
-----------------------------------------------------------
Guys, please let me know, if there is more profiling context i am missing.

Happy Coding... :):)
Rohit