SLCrDaJJ

1-- Declare variables
2Declare @DATABASE_NAME varchar(50) = 'shinhan';
3Declare @SCHEMA_NAME varchar(50) = 'dbo';
4
5-- Generate SQL script to union all table from the same group
6-- Example: alert_ofac_APR_2022 and alert_ofac_JAN_2022
7Declare @CONCAT_ALL_TABLE varchar(max) = '';
8Select
9	@CONCAT_ALL_TABLE = @CONCAT_ALL_TABLE + '
10Union All 
11Select * From ' + + @DATABASE_NAME + '.' + @SCHEMA_NAME + '.' + Table_Name + ' 
12'
13FROM
14	INFORMATION_SCHEMA.TABLES
15WHERE
16	TABLE_NAME LIKE 'EPHIST%'
17ORDER BY
18	TABLE_NAME
19Set
20	@CONCAT_ALL_TABLE = Stuff(@CONCAT_ALL_TABLE, 1, 10, '');
21
22PRINT(@CONCAT_ALL_TABLE);
23
24
25-- Create a table from from Union all tables genrate from script above (Paste the result from above) 
26-- DON'T NEED TO RUN THIS IN SHINHAN (still not need, all tables from Shinhan are different) 
27IF NOT EXISTS (SELECT * FROM EPHIST_FULL)
28BEGIN
29
30CREATE TABLE EPHIST_FULL FROM ()
31	Select * From EPHIST1_2022_SEP
32)
33END;
34
35
36--Create view of all column infomations
37CREATE VIEW COLUMNS_INFO AS (
38SELECT
39	*
40FROM
41	INFORMATION_SCHEMA.COLUMNS
42)
43;
44
45
46-- Create table validate_data store informations from all columns to validate data from each column.
47CREATE TABLE shinhan.dbo.validate_data (
48	TABLE_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
49	TABLE_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
50	TABLE_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
51	COLUMN_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
52);
53
54-- Populate data from INFORMATION_SCHEMA.COLUMNS into validate_data
55INSERT INTO validate_data (TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
56SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
57FROM COLUMNS_INFO;
58
59-- Alter validate_data able and add validate requirement data columns and set default to 1 (true)
60-- Added columns: C_1, R_1, A_1, A_2, A_3, A_4
61ALTER TABLE validate_data
62ADD C_1 bit not null default 1;
63ALTER TABLE validate_data
64ADD R_1 bit not null default 1;
65ALTER TABLE validate_data
66ADD A_1 bit not null default 1;
67ALTER TABLE validate_data
68ADD A_2 bit not null default 1;
69ALTER TABLE validate_data
70ADD A_3 bit not null default 1;
71ALTER TABLE validate_data
72ADD A_4 bit not null default 1;
73
74
75-- START VALIDATE DATA --
76-- A while loop though each Rows in table validate_data to get TABLE_NAME and COLUMN_NAME
77-- => Generate script to go through all data from that COLUMN_NAME from TABLE_NAME and validate data if it met the requirement.
78-- => Execute each statement after being generated
79
80
81-- Validate data C.1
82DECLARE @counter INT = 0; -- Decalre Counter variable to run While loop
83Declare @VALIDATE_SQL_C_1 nvarchar(max) = '';
84-- While loop Generate SQL script to validate data and execute each statement
85--Example output:
86	--	IF ( SELECT COUNT(*) FROM COLUMNS_INFO WHERE LEN([CHARACTER_MAXIMUM_LENGTH]) = 0 OR [CHARACTER_MAXIMUM_LENGTH] IS NULL )  > 0 
87	--	BEGIN
88	--	UPDATE validate_data SET C_1 = 0
89	--	WHERE TABLE_NAME = 'COLUMNS_INFO'
90	--	AND COLUMN_NAME = 'CHARACTER_MAXIMUM_LENGTH';
91	--	END
92WHILE @counter < (SELECT COUNT(*) FROM validate_data)
93BEGIN
94Select
95	@VALIDATE_SQL_C_1 =
96	'IF '
97	+ '( ' 
98	+ 'SELECT COUNT(*) FROM ' + TABLE_NAME + ' ' 
99	+ 'WHERE ' 
100	+ 'LEN(' + '[' +  COLUMN_NAME + ']' + ') = 0 ' -- Check if length of a data point is = 0
101	+ 'OR ' + '[' +  COLUMN_NAME + '] ' + 'IS NULL ' -- Check if data point is null
102	+ ') ' 
103	+ ' > 0
104' -- If > 0 of column satisfy one of the above requirement, set the C_1 requirement to 0 (false)
105	+ 'BEGIN
106'
107	+ 'UPDATE ' + 'validate_data '
108	+ 'SET C_1 = 0
109'
110	+ 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
111'
112	+ 'AND COLUMN_NAME = ' + '''' +  COLUMN_NAME + ''';
113'
114	+ 'END
115
116'
117FROM
118	validate_data
119ORDER BY
120	TABLE_NAME
121OFFSET @counter ROWS FETCH NEXT 1 ROWS ONLY; -- Set Offset to counter to skips through executed statements
122	;
123SET @counter = @counter + 1;
124
125-- Print and Execute script
126PRINT(@VALIDATE_SQL_C_1);
127EXEC(@VALIDATE_SQL_C_1);
128
129SET @VALIDATE_SQL_C_1 = ''
130END;
131
132
133-- Validate data R.1
134Declare @VALIDATE_SQL_R_1 nvarchar(max) = '';
135SET @counter = 0;
136-- While loop Generate SQL script to validate data and execute each statement
137--Example output
138	--IF ( SELECT COUNT(sub_table.counter) FROM (SELECT COUNT(*) as counter FROM EPHIST2_2022_SEP GROUP BY [overide_funds_balance_loaded] HAVING COUNT([overide_funds_balance_loaded]) > 1) as sub_table ) > 0
139	--BEGIN
140	--UPDATE validate_data SET R_1 = 0
141	--WHERE TABLE_NAME = 'EPHIST2_2022_SEP'
142	--AND COLUMN_NAME = 'overide_funds_balance_loaded';
143	--END
144WHILE @counter < (SELECT COUNT(*) FROM validate_data)
145BEGIN
146Select
147	@VALIDATE_SQL_R_1 = 
148	'IF '
149	+ '( SELECT COUNT(sub_table.counter) FROM (' 
150	+ 'SELECT COUNT(*) as counter FROM ' + TABLE_NAME + ' ' + 'GROUP BY ' + '[' + COLUMN_NAME + '] ' + 'HAVING COUNT(' + '[' + COLUMN_NAME + '])' + ' > 1' 
151	+ ') as sub_table )' -- Check if there is a data point that appear > 1 in table
152	+ ' > 0
153' -- If > 0 of column satisfy one of the above requirement, set the R_1 requirement to 0 (false)
154	+ 'BEGIN
155'
156	+ 'UPDATE ' + 'validate_data '
157	+ 'SET R_1 = 0
158'
159	+ 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
160'
161	+ 'AND COLUMN_NAME = ' + '''' +  COLUMN_NAME + ''';
162'
163	+ 'END
164
165'
166FROM
167	validate_data
168ORDER BY
169	TABLE_NAME
170OFFSET @counter ROWS FETCH NEXT 1 ROWS ONLY;
171	;
172SET @counter = @counter + 1;
173
174-- Print and Execute script
175PRINT(@VALIDATE_SQL_R_1);
176EXEC(@VALIDATE_SQL_R_1);
177
178SET @VALIDATE_SQL_R_1 = ''
179END;
180
181-- END VALIDATE DATA --