SQL Server: Search all tables for all rows with a specific value in a column

Question

(I've seen a couple of other posts about this on StackOverflow -- specifically this and this, but they both seem to be Oracle-specific, and I'm using SQL Server.)

So I've used this post to get all tables that have a specific column. That part works great, and it returns me over 100 tables. This is what I'm using, from that post:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE 'myColumn'
ORDER BY    TableName
            ,ColumnName;

What I'm now looking for is a modification to that -- is there a way to get all of the rows in each of those tables where my target column name has a specific value? Basically, I want to be able to rip through every table in the system and select every row where a specific column name has a specific value. (I'll be trying to delete all those rows later, but SELECT first, DELETE later.)

Something along the lines of this (which I know doesn't work; it's for illustration purposes):

SELECT [row]
    FROM sys.tables tab
    JOIN sys.columns col ON col.object_id = tab.object_id
    WHERE col.name LIKE 'myColumn' AND col.value = 'myVal'

The only other option I can think of is to take the output from the first query and run a SELECT on each one, which would give me unwieldy output in SQL Server Management Studio, to say the least.

Is there a simple way to go about this, without resorting to external tools or whatnot?

score 2 · Answer 1 · answered Feb 15 '17 at 23:04

2

Use INFORMATION_SCHEMA catalogs.

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%SOME_TEXT%'
ORDER BY TABLE_CATALOG, TABLE_NAME, COLUMN_NAME

Check it here: http://rextester.com/JPEZN41582

answered Feb 15 '17 at 23:04

McNets

10,352
3
32
61

The problem is, I can already get all the tables that have the column in it. I need all the rows that have a specific value in all the tables that have the column in it. – Ari Roth Feb 16 '17 at 20:04
Then you need a dynamic query: http://stackoverflow.com/a/591991/3270427 – McNets Feb 16 '17 at 20:20

Shaakir · Answer 2 · 2022-11-04T08:52:24.980

below is what i used to find all tables containing specific value for a specific column name

DECLARE @TableName varchar(250);
DECLARE @ColumnName varchar(250);
DECLARE @SearchValue varchar(250) = 'MyValue';
DECLARE @SearchColumn varchar(250)  = '%MyColumnName%';
DECLARE @SQLQuery AS NVARCHAR(500);

CREATE TABLE #TempTable(
TableName varchar(250),
ColumnName varchar(250),
)

DECLARE CUR_TEST CURSOR FAST_FORWARD FOR
    SELECT     t.name AS 'TableName', c.name as col_name
    FROM        sys.columns c
    JOIN        sys.tables  t   ON c.object_id = t.object_id
    WHERE       c.name LIKE @SearchColumn
    ORDER BY    TableName;

OPEN CUR_TEST
FETCH NEXT FROM CUR_TEST INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLQuery = 'insert into #TempTable(TableName, ColumnName) 
    select DISTINCT ''' + @TableName + ''', ''' + @ColumnName + ''' from ' + @TableName + ' WHERE ' + @ColumnName + ' = ''' + @SearchValue + ''''

    EXECUTE sp_executesql @SQLQuery

   FETCH NEXT FROM CUR_TEST INTO @TableName, @ColumnName
END
CLOSE CUR_TEST
DEALLOCATE CUR_TEST
GO

select * from #TempTable
Drop Table #TempTable

SQL Server: Search all tables for all rows with a specific value in a column

2 Answers2

Linked