0

I have 40+ tables, based on a certain columns limit, need to get a natural key (this is a natural key of a table which exceeded the threshold limit) and a column name, column value which exceeded the limit.

Example: table f_revenue has a column rev_amt. Natural key column are revnbr and lastupdated. If rev_amt > 3000000 then insert the natural key (revnbr, lastupdated), f_revenue, rev_amt, rev_amt exceeded 3000000

How do you create a procedure in SQL Server to dynamically scroll through 40+ tables and do as the example above?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rp2018
  • 61
  • 4
  • 6
  • 2
    Possible duplicate of [How to loop through all SQL tables?](https://stackoverflow.com/questions/26496864/how-to-loop-through-all-sql-tables) – tweray Nov 06 '18 at 15:15

1 Answers1

0

This should work. (spring boarded from the comment above)

DECLARE @NAME VARCHAR(100)
DECLARE @SQL NVARCHAR(300)

DECLARE CUR CURSOR FOR
SELECT a.NAME 
FROM   SYS.TABLES a JOIN 
SYS.COLUMNS b ON a.object_id=b.object_id 
WHERE b.name IN ('nacturalkey', 'f_revenue', 'rev_amt') 

OPEN CUR

FETCH NEXT FROM CUR INTO @NAME

WHILE @@FETCH_STATUS = 0
  BEGIN 

      SET @SQL = 'SELECT nacturalkey, f_revenue, rev_amt FROM '+@Name+' WHERE rev_amt > 3000000' 

      PRINT @SQL
      EXEC Sp_executesql
        @SQL

      FETCH NEXT FROM CUR INTO @NAME
  END

CLOSE CUR
lije
  • 420
  • 2
  • 15