I have Tables A, B, and C in mysql DataBase. Table A has columns D, E, and F, table B has G, H, and I columns, while table C has columns E, I, and J.
Now i passed separate string arrays of tables and columns to a stored procedure:
BEGIN
DECLARE @arrOftables VARCHAR(200);
DECLARE @arrOftables VARCHAR(200);
SET @arrOftables = "A, B, C,";
SET @arrOfcolumns = "D, E, F, G, H, I, J,";
END
Without creating a loop, strictly with information schema. I needed an sql statement that will fetch false if none of the columns exist in the three given tables, true otherwise. that is, tables A, B, and C must exist, while absence of columns D, E, F, G, H, I, J will flag false(0) true(1).at the moment, this is what i can think and its failing me;
SELECT count(1)
FROM information_schema.tables
WHERE table_name IN ( CONCAT('\'', REPLACE(@arrOftables, '\,', ' \'\, \''), '\'' ))
AND ( SELECT count(1)
FROM information_schema.columns
WHERE column_name IN (REPLACE(@arrOfcolumns, '\,', ' \'\, \'')))
AND table_schema = @DBName
INTO cnt;
Any assistance please ?