Return Column and Table holding value

Question

I can write a query to search for a table that has a particular column in a DB

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%A'

but My Question is:

can I search an entire DB for a value in a column? So I'm unsure the name of the column and I am unsure the name of the DB table but I know the value is 'Active'

take a look at this question, is SQL Server, but can be useful: https://stackoverflow.com/questions/709120/how-to-search-for-one-value-in-any-column-of-any-table-inside-one-ms-sql-databas — Horaciux, Sep 21 '17 at 02:25

score 1 · Answer 1 · answered Sep 21 '17 at 03:08

Yes, you can. In that case, you need to prepare dynamic query once you get list of tables, which consists column, which actually you are looking for.

Now create a cursor for

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%A'

Using above cursor loop below

SET @s = CONCAT("select count(*) from [tablename] where [columnname] like ","'%SOMETHING%'");
PREPARE stmt FROM @s
execute stmt;
DEALLOCATE PREPARE stmt;

Return Column and Table holding value

1 Answers1