0

(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?

Community
  • 1
  • 1
Ari Roth
  • 5,118
  • 2
  • 27
  • 42

1 Answers1

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

McNets
  • 9,869
  • 3
  • 31
  • 54
  • 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