CODEWITHSUNDEEP
sqloracle-database
Chazz1
Chazz1

Reputation: 133

Looking for a value in All tables in sql

Using SQL i have a series of table names:

  SELECT TNAME FROM SYS.COL WHERE CNAME='COLUMN_XPTO' AND TNAME LIKE '%SOMETHING%';

That returns a table with one column with table names containing the required values, or column.

I now want to iterate between those tables looking for:

  SELECT * FROM x WHERE COLUMN_XPTO='someValue';

where x is a table from the previous select. How can i do this?

Upvotes: 0

Views: 65

Answers (1)

AlvaroCryptogram
AlvaroCryptogram

Reputation: 408

declare @tablename varchar(200)
declare c1 cursor for select table_Name from information_schema.columns where column_name = 'myColumn'
open c1
while 0=0
begin
    fetch next from c1 into @tablename
    if @@FETCH_STATUS = 0
    begin
        exec('select * from ' + @tablename + ' where myColumn= ''myValue''')
    end
    else
        break
end
close c1
deallocate c1

Upvotes: 1

Related Questions