· 2 years ago · Jan 03, 2023, 01:30 PM
1--Declare @CONCAT_ALL_TABLE varchar(max) = '';
2--Select
3-- @CONCAT_ALL_TABLE = @CONCAT_ALL_TABLE + 'Union All Select * From ' + Table_Name + ' '
4--FROM
5-- INFORMATION_SCHEMA.TABLES
6--WHERE
7-- TABLE_NAME LIKE 'alert_ofac%'
8--ORDER BY
9-- TABLE_NAME
10--Set
11-- @CONCAT_ALL_TABLE = Stuff(@CONCAT_ALL_TABLE, 1, 10, '');
12--
13--PRINT(@CONCAT_ALL_TABLE);
14
15--IF NOT EXISTS (SELECT * FROM dbo.views WHERE name = 'CONCAT_ALL_TABLE')
16--BEGIN
17--CREATE VIEW CONCAT_ALL_TABLE AS (
18-- Select * From alert_ofac_APR_2022
19-- Union All
20-- Select * From alert_ofac_AUG_2022
21-- Union All
22-- Select * From alert_ofac_DEC_2022
23-- Union All
24-- Select * From alert_ofac_FEB_2022
25-- Union All
26-- Select * From alert_ofac_JAN_2022
27-- Union All
28-- Select * From alert_ofac_JUL_2022
29-- Union All
30-- Select * From alert_ofac_JUN_2022
31-- Union All
32-- Select * From alert_ofac_MAR_2022
33-- Union All
34-- Select * From alert_ofac_MAY_2022
35-- Union All
36-- Select * From alert_ofac_NOV_2022
37-- Union All
38-- Select * From alert_ofac_OCT_2022
39-- Union All
40-- Select * From alert_ofac_SEP_2022
41--)
42--END;
43
44--select *
45--from INFORMATION_SCHEMA.COLUMNS
46--where TABLE_NAME='CONCAT_ALL_TABLE';
47
48--CREATE TABLE newbank.dbo.validate_data (
49-- Column1 varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
50-- TABLE_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
51-- TABLE_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
52-- TABLE_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
53-- COLUMN_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
54-- ORDINAL_POSITION int NULL,
55-- COLUMN_DEFAULT varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
56-- IS_NULLABLE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
57-- DATA_TYPE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
58-- CHARACTER_MAXIMUM_LENGTH varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
59-- CHARACTER_OCTET_LENGTH varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
60-- NUMERIC_PRECISION varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
61-- NUMERIC_PRECISION_RADIX varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
62-- NUMERIC_SCALE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
63-- DATETIME_PRECISION varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
64-- CHARACTER_SET_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
65-- CHARACTER_SET_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
66-- CHARACTER_SET_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
67-- COLLATION_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
68-- COLLATION_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
69-- COLLATION_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
70-- DOMAIN_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
71-- DOMAIN_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
72-- DOMAIN_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
73--);
74
75-- SELECT
76-- TABLE_NAME
77-- FROM
78-- INFORMATION_SCHEMA.TABLES
79-- WHERE
80-- TABLE_SCHEMA = @DATABASE_SCHEMA_NAME
81-- AND
82-- TABLE_NAME LIKE 'alert_ofac%';
83
84
85-- Create view of all column infomations
86--CREATE VIEW COLUMNS_INFO AS (
87--SELECT *
88--FROM INFORMATION_SCHEMA.COLUMNS
89--WHERE TABLE_NAME LIKE 'alert_ofac%'
90--)
91--;
92
93--SELECT * FROM COLUMNS_INFO;
94
95-- Example of check if C.1 satisfy
96--SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM COLUMNS_INFO
97--WHERE [ACCOUNT ID] IS NULL
98--OR
99--[ACCOUNT ID] = 'NULL'
100--OR
101--[ACCOUNT ID] ='None'
102--OR
103--LEN([ACCOUNT ID]) = 0
104--;
105
106--INSERT INTO validate_data (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMNS_INFO)
107--SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMNS_NAME
108-- CASE (SELECT COUNT(*) > 0 FROM TABLE_SCHEMA.TABLE_NAME))
109
110--CREATE TABLE table_information (
111-- column_name varchar(255) NOT NULL,
112-- is_null varchar(5) NOT NULL
113--);
114
115--INSERT INTO validate_data (TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA,CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME,DOMAIN_CATALOG,DOMAIN_SCHEMA,DOMAIN_NAME)
116--SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA,CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME,DOMAIN_CATALOG,DOMAIN_SCHEMA,DOMAIN_NAME
117--FROM
118--COLUMNS_INFO;
119--
120--ALTER TABLE validate_data
121--ADD C_1 bit not null default 1;
122--
123--ALTER TABLE validate_data
124--ADD R_1 bit not null default 1;
125--
126--ALTER TABLE validate_data
127--ADD A_1 bit not null default 1;
128--
129--
130--ALTER TABLE validate_data
131--ADD A_2 bit not null default 1;
132--
133--
134--ALTER TABLE validate_data
135--ADD A_3 bit not null default 1;
136--
137--ALTER TABLE validate_data
138--ADD A_4 bit not null default 1;
139
140
141DECLARE @counter INT = 0;
142
143
144--Declare @VALIDATE_SQL_C_1 nvarchar(max) = '';
145--WHILE @counter < (SELECT COUNT(*) FROM validate_data)
146--BEGIN
147--Select
148-- @VALIDATE_SQL_C_1 = @VALIDATE_SQL_C_1 +
149-- 'IF '
150-- + '( '
151-- + 'SELECT COUNT(*) FROM ' + TABLE_NAME + ' ' + 'WHERE ' + 'LEN(' + '[' + COLUMN_NAME + ']' + ') = 0'
152-- + ') '
153-- + ' > 0
154--'
155-- + 'BEGIN
156--'
157-- + 'UPDATE ' + 'validate_data '
158-- + 'SET C_1 = 0
159--'
160-- + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
161--'
162-- + 'AND COLUMN_NAME = ' + '''' + COLUMN_NAME + ''';
163--'
164-- + 'END
165--
166--'
167--FROM
168-- validate_data
169--ORDER BY
170-- TABLE_NAME
171--OFFSET @counter ROWS FETCH NEXT 10 ROWS ONLY;
172-- ;
173--PRINT(@VALIDATE_SQL_C_1);
174--SET @counter = @counter + 10;
175--END;
176--
177--EXEC (@VALIDATE_SQL_C_1);
178--
179--
180--
181--Declare @VALIDATE_SQL_R_1 nvarchar(max) = '';
182--SET @counter = 0;
183--WHILE @counter < (SELECT COUNT(*) FROM validate_data)
184--BEGIN
185--Select
186-- @VALIDATE_SQL_R_1 = @VALIDATE_SQL_R_1 +
187-- 'IF '
188-- + '( SELECT COUNT(sub_table.counter) FROM ('
189-- + 'SELECT COUNT(*) as counter FROM ' + TABLE_NAME + ' ' + 'GROUP BY ' + '[' + COLUMN_NAME + '] ' + 'HAVING COUNT(' + '[' + COLUMN_NAME + '])' + ' > 1'
190-- + ') as sub_table )'
191-- + ' > 0
192--'
193-- + 'BEGIN
194--'
195-- + 'UPDATE ' + 'validate_data '
196-- + 'SET R_1 = 0
197--'
198-- + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
199--'
200-- + 'AND COLUMN_NAME = ' + '''' + COLUMN_NAME + ''';
201--'
202-- + 'END
203--
204--'
205--FROM
206-- validate_data
207--ORDER BY
208-- TABLE_NAME
209--OFFSET @counter ROWS FETCH NEXT 10 ROWS ONLY;
210-- ;
211--PRINT(@VALIDATE_SQL_R_1);
212--SET @counter = @counter + 10;
213--END;
214--
215--EXEC (@VALIDATE_SQL_R_1);
216
217
218
219
220Declare @VALIDATE_SQL_A_1 nvarchar(max) = '';
221SET @counter = 0;
222WHILE @counter < (SELECT COUNT(*) FROM validate_data)
223BEGIN
224Select
225 @VALIDATE_SQL_R_1 = @VALIDATE_SQL_R_1 +
226 'IF '
227 + '( SELECT COUNT(sub_table.counter) FROM ('
228 + 'SELECT COUNT(*) as counter FROM ' + TABLE_NAME + ' ' + 'GROUP BY ' + '[' + COLUMN_NAME + '] ' + 'HAVING COUNT(' + '[' + COLUMN_NAME + '])' + ' > 1'
229 + ') as sub_table )'
230 + ' > 0
231'
232 + 'BEGIN
233'
234 + 'UPDATE ' + 'validate_data '
235 + 'SET R_1 = 0
236'
237 + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
238'
239 + 'AND COLUMN_NAME = ' + '''' + COLUMN_NAME + ''';
240'
241 + 'END
242
243'
244FROM
245 validate_data
246ORDER BY
247 TABLE_NAME
248OFFSET @counter ROWS FETCH NEXT 10 ROWS ONLY;
249 ;
250PRINT(@VALIDATE_SQL_R_1);
251SET @counter = @counter + 10;
252END;
253
254EXEC (@VALIDATE_SQL_R_1);
255
256
257