How get information from multiple tables using cur

问题:

I have a query, that returns multiple tables, something like that:

SELECT TableName, DatabaseName +'.'+ TableName, ColumnName
FROM DBC.Columns
WHERE ColumnName = 'id'

And I need to loop through these tables by looking to the information stored in these tables, in order to get only specific tables.

I tried something like code below, using 'LOOP' and cursor, but it says that Query is invalid (code have been taken from here):

DECLARE cursor_Tables CURSOR FOR     
    SELECT DatabaseName || '.' || TableName
    FROM   DBC.Columns
    WHERE  ColumnName  ='id'; 

OPEN cursor_Tables; 
    label1: 
    LOOP    
        FETCH  cursor_Tables into tbName;
        IF (SQLSTATE ='02000') THEN
            LEAVE label1;
        END IF;

        CASE WHEN (  
            SELECT COUNT(*)
            FROM prd3_db_tmd.K_PTY_NK01
            WHERE id = 0 ) > 0
             THEN tbName
        END 
    END LOOP label1;
CLOSE cursor_Tables;
END;

How can I actually deal with this problem? Do I need to use procedure in addition? DBMS is Teradata

回答1:

You need a Stored Procedure because this is the only place where you can use a cursor in Teradata.

REPLACE PROCEDURE testproc()
DYNAMIC RESULT SETS 1
BEGIN
   DECLARE tbName VARCHAR(257);
   DECLARE SqlStr VARCHAR(500);

   -- temporary table to store the result set
   CREATE VOLATILE TABLE _vt_(tbName VARCHAR(257)) ON COMMIT PRESERVE ROWS;

   -- your existing query to return the table name
   -- Better use ColumnsV instead of Columns
   FOR cursor_Tables AS    
       SELECT DatabaseName || '.' || TABLENAME AS tbName
       FROM   DBC.ColumnsV
       WHERE  ColumnName  ='id'
   DO -- prepare the dynamic SQL ...
      SET SqlStr = 
         'insert into _vt_
          select ''' || cursor_tables.tbName || ''' 
          from ' || cursor_tables.tbName || '
          where id = 0 
          having count(*) > 0;
          ';
      -- ... and run it
      EXECUTE IMMEDIATE SqlStr;
   END FOR;

   BEGIN -- return the result set
      DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
      SET SqlStr = 'SELECT * FROM _vt_;';
      PREPARE S1 FROM SqlStr;
      OPEN resultset;
   END;

   DROP TABLE vt;
END;

回答2:

If this is SQL Server you can check following SQL cursor, I edited the cursor declaration and the code within Although they may differ from your requirement, I think you can modify easily

declare @sql nvarchar(max)
declare @tablename nvarchar(100)

DECLARE cursor_Tables CURSOR FOR     
    SELECT s.name + '.' + o.name 
        --s.name [schema], o.name [table]
    FROM   sys.Columns c
    inner join sys.objects o on c.object_id = o.object_id
    inner join sys.schemas s on s.schema_id = o.schema_id
    WHERE  c.Name  ='id' and o.type = 'U'

/*
SELECT TableName, DatabaseName +'.'+ TableName, ColumnName
FROM DBC.Columns
WHERE ColumnName = 'id'
*/
OPEN cursor_Tables; 

FETCH NEXT FROM cursor_Tables INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

--  print @tablename
set @sql = 'select case when count(*) > 0 then ''' + @tablename + ''' else '''' end from ' + @tablename
exec sp_executesql @sql

 FETCH NEXT FROM cursor_Tables INTO @tablename
END

CLOSE cursor_Tables;
DEALLOCATE cursor_Tables;

回答3:

On SQL Server, sp_MsForEachTable undocumented stored procedure can be used instead of a loop structure like a cursor

Please check the below SQL command

EXEC sp_MSForEachTable 'IF EXISTS(select * from sys.columns where name = ''Id'' and object_id = object_id(''?''))SELECT ''?'', COUNT(*) FROM ?'

The syntax may be difficult if you are using the sp_msforeachtable or sp_msforeachdb, but you can find samples on the web

回答4:

You could create a variable to hold the number of rows and set it equal to the count:

DECLARE @count INT

SELECT @count = COUNT(*)
FROM prd3_db_tmd.K_PTY_NK01
WHERE id = 0

Then use an if statement to select the table if it has rows that meet your criteria:

IF @count > 0
BEGIN
    SELECT tbName
END

---

Also as a side note without having SELECT in front of your CASE statement the syntax is invalid, you may want to try it with just adding SELECT in front of CASE if you don't like the way mentioned above

回答5:

You need to use dynamic SQL. If you need to see the info on the table, you can create a synonym.

    CURSOR  cursor_Tables is
        SELECT DatabaseName || '.' || TableName AS tbName 
        FROM   DBC.Columns
        WHERE  ColumnName  ='id'; 

begin  
    FOR R IN cursor_Tables
    LOOP    

    execute immediate 'CREATE OR REPLACE SYNONYM your_synonym FOR '|| R.tbName ;

    select *
    from your_synonym;


    END LOOP;
END;

Or if you want you can create a view.