0

I'm using MySQL and we use a Deleted column to do "soft" deletes and keep history. I'm trying to write either a procedure or SQL statement which can get me the count from each table.

I believe this should be possible, as it somewhat reminds me of a SQL injection attack I've seen before. The difference is I want to use this for good purposes :)

I reworked the code shown there, but think my syntax may be off for MySQL; how can I correct this, or should I find a better way of accomplishing this? Below is reworked from the link (so pretty sure it doesn't follow the MySQL procedure syntax).

Here is what I have so far:

Set ansi_warnings off

Declare @T VARCHAR(255)

DECLARE Table_Cursor CURSOR FOR
  Select
    c.TABLE_NAME
  from INFORMATION_SCHEMA.columns c, INFORMATION_SCHEMA.tables t
  where 
    t.table_name = c.table_name
    and t.table_type = 'BASE TABLE'
    AND c.COLUMN_NAME = 'Deleted'

OPEN Table_Cursor

FETCH NEXT FROM Table_Cursor INTO @T
WHILE(@@FETCH_STATUS=0)
BEGIN
    EXEC ( 'SELECT COUNT(*) AS ' + @T + 'Count FROM ' + @T + ' WHERE Deleted IS NULL' );
    FETCH NEXT FROM Table_Cursor INTO @T
END

CLOSE Table_Cursor

DEALLOCATE Table_Cursor
halfer
  • 19,824
  • 17
  • 99
  • 186
James Oravec
  • 19,579
  • 27
  • 94
  • 160

1 Answers1

1

I would guess that helps. Instead of 

BEGIN
    EXEC ( 'SELECT COUNT(*) AS ' + @T + 'Count FROM ' + @T + ' WHERE Deleted IS NULL' );

    # would you not store that result somewhere?
    FETCH NEXT FROM Table_Cursor INTO @T
END

just try 

BEGIN

    EXEC ( 'SELECT "@T" as tabname, COUNT(*) AS del_Count FROM ' + @T + ' WHERE Deleted IS NULL' );
    # somewhere store the number and the tabname
    FETCH NEXT FROM Table_Cursor INTO @T
END
flaschenpost
  • 2,205
  • 1
  • 14
  • 29