Check information_schema for absence of columns for given tables

Question

0.00/5 (No votes)

See more:

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:

SQL

/*Never mind the procedur's detail*/
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;

SQL

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;   /*variable cnt had been declared before now*/

/*
   Never mind my CONCAT() and REPLACE(). just using them
   to format some stupid characters
*/

Any assistance please ?

Posted 17-Jun-15 6:57am

Member 11762344

Add a Solution

Comments

CHill60 18-Jun-15 11:27am

Personally I would split the input strings out into temporary tables (example[^] of how to do that) then join the schema tables to the list of names in those temp tables.
As your sql stands that IN clause is just wrong.
Either that or build up some dynamic sql and sp_execute it.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Corporal Agarn · Answer 1 · 2015-06-18T06:10:00

Solution 1

Try creating a table of column/table names then using

SQL

SELECT
    T.[name]        AS Table_Name,
    AC.[name]       AS Column_Name
FROM sys.tables AS T
INNER JOIN sys.all_columns AC ON T.[object_id] = AC.[object_id]

with a cross join

Posted 18-Jun-15 6:10am

Corporal Agarn

Comments

CHill60 18-Jun-15 12:40pm

How do they then check that against the input?

CHill60 · Answer 2 · 2015-06-18T06:44:00

Your code looks like a strange mix of C# and SQL - pasting it into SSMS shows syntax errors. In any event you need to create dynamic sql ( a string representing the query) if you are going to try that REPLACE stuff.

Using the method I suggested in my comment - the following has been tested and works

IF OBJECT_ID('tempdb.dbo.#tables') IS NOT NULL  drop table #tables
IF OBJECT_ID('tempdb.dbo.#columns') IS NOT NULL  drop table #columns

-- break down the input into temporary tables
select * into #tables from dbo.fnSplitString(@arrOftables, ',')
select * into #columns from dbo.fnSplitString(@arrOfcolumns, ',')

-- tidy up any spaces from the input (or do this in the function)
update #tables set splitdata = LTRIM(RTRIM(splitdata))
update #columns set splitdata = LTRIM(RTRIM(splitdata))

--Posit that we have 'failed'
DECLARE @retValue varchar(5) = 'False'

-- check the tables all exist
DECLARE @expect int = (SELECT COUNT(*) FROM #tables)
DECLARE @res int
select @res = COUNT(1) from #tables B 
INNER JOIN INFORMATION_SCHEMA.tables A ON A.TABLE_NAME = B.splitdata

-- if all tables exist move on to check the columns
IF @res = @expect
BEGIN
	SET @expect = (SELECT COUNT(*) FROM #columns)

	SELECT @res = COUNT(*) FROM information_schema.columns B 
	INNER JOIN INFORMATION_SCHEMA.tables A ON A.TABLE_NAME = B.TABLE_NAME 
	INNER JOIN #tables T ON A.TABLE_NAME = T.splitdata
	INNER JOIN #columns C ON B.COLUMN_NAME = C.splitdata

	IF @res = @expect
	BEGIN
		-- all tables listed exist and all columns listed exist on those tables
		SET @retValue = 'True'
	END
END

PRINT @retValue -- RETURN value

I got the split function from http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/[^]