· 2 years ago · Jan 04, 2023, 03:00 PM
1-- Generate SQL script to union all table
2Declare @DATABASE_NAME varchar(50) = 'shinhan';
3Declare @SCHEMA_NAME varchar(50) = 'dbo';
4Declare @CONCAT_ALL_TABLE varchar(max) = '';
5Select
6 @CONCAT_ALL_TABLE = @CONCAT_ALL_TABLE + '
7Union All
8Select * From ' + + @DATABASE_NAME + '.' + @SCHEMA_NAME + '.' + Table_Name + '
9'
10FROM
11 INFORMATION_SCHEMA.TABLES
12WHERE
13 TABLE_NAME LIKE 'EPHIST%'
14ORDER BY
15 TABLE_NAME
16Set
17 @CONCAT_ALL_TABLE = Stuff(@CONCAT_ALL_TABLE, 1, 10, '');
18
19PRINT(@CONCAT_ALL_TABLE);
20
21
22-- Union all tables from script (still not need, all table are different)
23--IF NOT EXISTS (SELECT * FROM EPHIST_FULL)
24--BEGIN
25--CREATE TABLE EPHIST_FULL FROM ()
26-- Select * From EPHIST1_2022_SEP
27--)
28--END;
29
30
31--Create view of all column infomations
32CREATE VIEW COLUMNS_INFO AS (
33SELECT
34 *
35FROM
36 INFORMATION_SCHEMA.COLUMNS
37)
38;
39
40
41-- Create table Validate data
42CREATE TABLE shinhan.dbo.validate_data (
43 TABLE_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
44 TABLE_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
45 TABLE_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
46 COLUMN_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
47);
48-- Populate data from INFORMATION_SCHEMA.COLUMNS into validate_data
49INSERT INTO validate_data (TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
50SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
51FROM
52COLUMNS_INFO;
53-- Add validate data columns
54ALTER TABLE validate_data
55ADD C_1 bit not null default 1;
56ALTER TABLE validate_data
57ADD R_1 bit not null default 1;
58ALTER TABLE validate_data
59ADD A_1 bit not null default 1;
60ALTER TABLE validate_data
61ADD A_2 bit not null default 1;
62ALTER TABLE validate_data
63ADD A_3 bit not null default 1;
64ALTER TABLE validate_data
65ADD A_4 bit not null default 1;
66
67
68-- START VALIDATE DATA --
69
70-- Validate data C.1
71DECLARE @counter INT = 0;
72Declare @VALIDATE_SQL_C_1 nvarchar(max) = '';
73-- While loop Generate SQL script to validate data and execute each statement
74--Example output
75 -- IF ( SELECT COUNT(*) FROM COLUMNS_INFO WHERE LEN([CHARACTER_MAXIMUM_LENGTH]) = 0 OR [CHARACTER_MAXIMUM_LENGTH] IS NULL ) > 0
76 -- BEGIN
77 -- UPDATE validate_data SET C_1 = 0
78 -- WHERE TABLE_NAME = 'COLUMNS_INFO'
79 -- AND COLUMN_NAME = 'CHARACTER_MAXIMUM_LENGTH';
80 -- END
81WHILE @counter < (SELECT COUNT(*) FROM validate_data)
82BEGIN
83Select
84 @VALIDATE_SQL_C_1 =
85 'IF '
86 + '( '
87 + 'SELECT COUNT(*) FROM ' + TABLE_NAME + ' '
88 + 'WHERE '
89 + 'LEN(' + '[' + COLUMN_NAME + ']' + ') = 0 '
90 + 'OR ' + '[' + COLUMN_NAME + '] ' + 'IS NULL ' + ') ' + ' > 0
91'
92 + 'BEGIN
93'
94 + 'UPDATE ' + 'validate_data '
95 + 'SET C_1 = 0
96'
97 + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
98'
99 + 'AND COLUMN_NAME = ' + '''' + COLUMN_NAME + ''';
100'
101 + 'END
102
103'
104FROM
105 validate_data
106ORDER BY
107 TABLE_NAME
108OFFSET @counter ROWS FETCH NEXT 1 ROWS ONLY;
109 ;
110SET @counter = @counter + 1;
111
112-- Print and Execute script
113PRINT(@VALIDATE_SQL_C_1);
114EXEC(@VALIDATE_SQL_C_1);
115
116SET @VALIDATE_SQL_C_1 = ''
117END;
118
119
120-- Validate data R.1
121Declare @VALIDATE_SQL_R_1 nvarchar(max) = '';
122SET @counter = 0;
123-- While loop Generate SQL script to validate data and execute each statement
124--Example output
125 --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
126 --BEGIN
127 --UPDATE validate_data SET R_1 = 0
128 --WHERE TABLE_NAME = 'EPHIST2_2022_SEP'
129 --AND COLUMN_NAME = 'overide_funds_balance_loaded';
130 --END
131WHILE @counter < (SELECT COUNT(*) FROM validate_data)
132BEGIN
133Select
134 @VALIDATE_SQL_R_1 =
135 'IF '
136 + '( SELECT COUNT(sub_table.counter) FROM ('
137 + 'SELECT COUNT(*) as counter FROM ' + TABLE_NAME + ' ' + 'GROUP BY ' + '[' + COLUMN_NAME + '] ' + 'HAVING COUNT(' + '[' + COLUMN_NAME + '])' + ' > 1'
138 + ') as sub_table )'
139 + ' > 0
140'
141 + 'BEGIN
142'
143 + 'UPDATE ' + 'validate_data '
144 + 'SET R_1 = 0
145'
146 + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
147'
148 + 'AND COLUMN_NAME = ' + '''' + COLUMN_NAME + ''';
149'
150 + 'END
151
152'
153FROM
154 validate_data
155ORDER BY
156 TABLE_NAME
157OFFSET @counter ROWS FETCH NEXT 1 ROWS ONLY;
158 ;
159SET @counter = @counter + 1;
160
161-- Print and Execute script
162PRINT(@VALIDATE_SQL_R_1);
163EXEC(@VALIDATE_SQL_R_1);
164
165SET @VALIDATE_SQL_R_1 = ''
166END;
167
168-- END VALIDATE DATA --