--Declare @CONCAT_ALL_TABLE varchar(max) = '';
--Select
--  @CONCAT_ALL_TABLE = @CONCAT_ALL_TABLE + 'Union All Select * From ' + Table_Name + ' '
--FROM
--  INFORMATION_SCHEMA.TABLES
--WHERE
--  TABLE_NAME LIKE 'alert_ofac%'
--ORDER BY
--  TABLE_NAME
--Set
--  @CONCAT_ALL_TABLE = Stuff(@CONCAT_ALL_TABLE, 1, 10, '');
--
--PRINT(@CONCAT_ALL_TABLE);

--IF NOT EXISTS (SELECT * FROM dbo.views WHERE name = 'CONCAT_ALL_TABLE')
--BEGIN
--CREATE VIEW CONCAT_ALL_TABLE AS (
--  Select * From alert_ofac_APR_2022
--  Union All
--  Select * From alert_ofac_AUG_2022
--  Union All
--  Select * From alert_ofac_DEC_2022
--  Union All
--  Select * From alert_ofac_FEB_2022
--  Union All
--  Select * From alert_ofac_JAN_2022
--  Union All
--  Select * From alert_ofac_JUL_2022
--  Union All
--  Select * From alert_ofac_JUN_2022
--  Union All
--  Select * From alert_ofac_MAR_2022
--  Union All
--  Select * From alert_ofac_MAY_2022
--  Union All
--  Select * From alert_ofac_NOV_2022
--  Union All
--  Select * From alert_ofac_OCT_2022
--  Union All
--  Select * From alert_ofac_SEP_2022
--)
--END;

--select *
--from INFORMATION_SCHEMA.COLUMNS
--where TABLE_NAME='CONCAT_ALL_TABLE';

--CREATE TABLE newbank.dbo.validate_data (
--  Column1 varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  TABLE_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  TABLE_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  TABLE_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  COLUMN_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  ORDINAL_POSITION int NULL,
--  COLUMN_DEFAULT varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  IS_NULLABLE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  DATA_TYPE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  CHARACTER_MAXIMUM_LENGTH varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  CHARACTER_OCTET_LENGTH varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  NUMERIC_PRECISION varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  NUMERIC_PRECISION_RADIX varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  NUMERIC_SCALE varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  DATETIME_PRECISION varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  CHARACTER_SET_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  CHARACTER_SET_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  CHARACTER_SET_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  COLLATION_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  COLLATION_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  COLLATION_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  DOMAIN_CATALOG varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  DOMAIN_SCHEMA varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--  DOMAIN_NAME varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
--);

--  SELECT
--          TABLE_NAME
--  FROM
--          INFORMATION_SCHEMA.TABLES
--  WHERE
--          TABLE_SCHEMA = @DATABASE_SCHEMA_NAME
--  AND
--          TABLE_NAME LIKE 'alert_ofac%';


-- Create view of all column infomations
--CREATE VIEW COLUMNS_INFO AS (
--SELECT *
--FROM INFORMATION_SCHEMA.COLUMNS
--WHERE TABLE_NAME LIKE 'alert_ofac%'
--)
--;

--SELECT * FROM COLUMNS_INFO;

-- Example of check if C.1 satisfy
--SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM COLUMNS_INFO
--WHERE [ACCOUNT ID] IS NULL
--OR
--[ACCOUNT ID] = 'NULL'
--OR
--[ACCOUNT ID] ='None'
--OR
--LEN([ACCOUNT ID]) = 0
--;

--INSERT INTO validate_data (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMNS_INFO)
--SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMNS_NAME
--  CASE (SELECT COUNT(*) > 0 FROM TABLE_SCHEMA.TABLE_NAME))

--CREATE TABLE table_information (
--    column_name varchar(255) NOT NULL,
--    is_null varchar(5) NOT NULL
--);

--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)
--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
--FROM
--COLUMNS_INFO;
--
--ALTER TABLE validate_data
--ADD C_1 bit not null default 1;
--
--ALTER TABLE validate_data
--ADD R_1 bit not null default 1;
--
--ALTER TABLE validate_data
--ADD A_1 bit not null default 1;
--
--
--ALTER TABLE validate_data
--ADD A_2 bit not null default 1;
--
--
--ALTER TABLE validate_data
--ADD A_3 bit not null default 1;
--
--ALTER TABLE validate_data
--ADD A_4 bit not null default 1;


DECLARE @counter INT = 0;


--Declare @VALIDATE_SQL_C_1 nvarchar(max) = '';
--WHILE @counter < (SELECT COUNT(*) FROM validate_data)
--BEGIN
--Select
--  @VALIDATE_SQL_C_1 = @VALIDATE_SQL_C_1 +
--  'IF '
--  + '( '
--  + 'SELECT COUNT(*) FROM ' + TABLE_NAME + ' ' + 'WHERE ' + 'LEN(' + '[' +  COLUMN_NAME + ']' + ') = 0'
--  + ') '
--  + ' > 0
--'
--  + 'BEGIN
--'
--  + 'UPDATE ' + 'validate_data '
--  + 'SET C_1 = 0
--'
--  + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
--'
--  + 'AND COLUMN_NAME = ' + '''' +  COLUMN_NAME + ''';
--'
--  + 'END
--
--'
--FROM
--  validate_data
--ORDER BY
--  TABLE_NAME
--OFFSET @counter ROWS FETCH NEXT 10 ROWS ONLY;
--  ;
--PRINT(@VALIDATE_SQL_C_1);
--SET @counter = @counter + 10;
--END;
--
--EXEC (@VALIDATE_SQL_C_1);
--
--
--
--Declare @VALIDATE_SQL_R_1 nvarchar(max) = '';
--SET @counter = 0;
--WHILE @counter < (SELECT COUNT(*) FROM validate_data)
--BEGIN
--Select
--  @VALIDATE_SQL_R_1 = @VALIDATE_SQL_R_1 +
--  'IF '
--  + '( SELECT COUNT(sub_table.counter) FROM ('
--  + 'SELECT COUNT(*) as counter FROM ' + TABLE_NAME + ' ' + 'GROUP BY ' + '[' + COLUMN_NAME + '] ' + 'HAVING COUNT(' + '[' + COLUMN_NAME + '])' + ' > 1'
--  + ') as sub_table )'
--  + ' > 0
--'
--  + 'BEGIN
--'
--  + 'UPDATE ' + 'validate_data '
--  + 'SET R_1 = 0
--'
--  + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
--'
--  + 'AND COLUMN_NAME = ' + '''' +  COLUMN_NAME + ''';
--'
--  + 'END
--
--'
--FROM
--  validate_data
--ORDER BY
--  TABLE_NAME
--OFFSET @counter ROWS FETCH NEXT 10 ROWS ONLY;
--  ;
--PRINT(@VALIDATE_SQL_R_1);
--SET @counter = @counter + 10;
--END;
--
--EXEC (@VALIDATE_SQL_R_1);




Declare @VALIDATE_SQL_A_1 nvarchar(max) = '';
SET @counter = 0;
WHILE @counter < (SELECT COUNT(*) FROM validate_data)
BEGIN
Select
    @VALIDATE_SQL_R_1 = @VALIDATE_SQL_R_1 +
    'IF '
    + '( SELECT COUNT(sub_table.counter) FROM ('
    + 'SELECT COUNT(*) as counter FROM ' + TABLE_NAME + ' ' + 'GROUP BY ' + '[' + COLUMN_NAME + '] ' + 'HAVING COUNT(' + '[' + COLUMN_NAME + '])' + ' > 1'
    + ') as sub_table )'
    + ' > 0
'
    + 'BEGIN
'
    + 'UPDATE ' + 'validate_data '
    + 'SET R_1 = 0
'
    + 'WHERE TABLE_NAME = ' + '''' + TABLE_NAME + '''
'
    + 'AND COLUMN_NAME = ' + '''' +  COLUMN_NAME + ''';
'
    + 'END

'
FROM
    validate_data
ORDER BY
    TABLE_NAME
OFFSET @counter ROWS FETCH NEXT 10 ROWS ONLY;
    ;
PRINT(@VALIDATE_SQL_R_1);
SET @counter = @counter + 10;
END;

EXEC (@VALIDATE_SQL_R_1);