0

I want the user to input whatever database they want to check and whatever tables they want to check. For example the call would look like this: 

call tableCheck('MyDatabase', 'table1 table20 table3')

So the procedure would check for those tables (space delimited). I have my code down below but I am having problem checking the other tables, it reads the first but not the second or third one. SO my code is not looping or not reading the next word.

PROCEDURE `tableCheck` (db VARCHAR(256), db_tables VARCHAR(256))
BEGIN   
DECLARE tbl, tbls VARCHAR(256); 
DECLARE c INT DEFAULT 0;

SET tbls = db_tables;

    WHILE c = 0 DO

        #split word
        SET tbl = SUBSTRING_INDEX(tbls," ",1);
        #go to next string
        SET tbls = SUBSTR(tbls,LENGTH(tbl)+1);

        #check every table
        SELECT table_name AS 'Table Name'
        FROM INFORMATION_SCHEMA.TABLES  
        WHERE table_schema=db AND table_name=tbl;

        IF tbls = 0 THEN 
            SET c = 1;
        END IF;
    END WHILE;
END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jim
  • 27
  • 1
  • 6
  • Unless your tables start with numbers, `tbls = 0` will always be true. – Uueerdo Sep 30 '15 at 19:58
  • I see so its always closing after the first loop. So I have to find another way for the loop to close. @Uueerdo – Jim Sep 30 '15 at 20:07
  • You could make your test something like this `tbls REGEXP '[a-zA-Z]+'` – Uueerdo Sep 30 '15 at 20:46
  • I've tried different variations of this but same result, either closes the loop right away or goes infinite @Uueerdo – Jim Sep 30 '15 at 21:45

1 Answers1

0

I have given this link out a few times today...so maybe it will work for you too. Convert a delimited string to a list of values

Using the dbo.fnArray function from that link you should be able to join on the INFORMATION_SCHEMA.TABLES like this:

create procedure dbo.spCheckTable @db varchar(256), @tbls varchar(8000)
as
begin
    select * 
      from INFORMATION_SCHEMA.TABLES i
     inner join dbo.fnArray(@tbls, ' ') t on i.TABLE_NAME = t.arrValue
     where i.TABLE_CATALOG = @db
end

Now its a stored procedure

Community
  • 1
  • 1
ewahner
  • 1,149
  • 2
  • 11
  • 23
  • Ah, I see what you're doing but I want to avoid using functions. I want to keep it all in one stored procedure – Jim Sep 30 '15 at 20:15
  • Updated my solution so now its a stored procedure. Not sure why you want to avoid functions. Most people strive to implement DRY principles and other patterns. – ewahner Oct 01 '15 at 11:57