Dynamically Query All SQL Tables Matching Certain Criteria

Question

I have a SQL Server database with dozens of tables that contain HTML content. I need to query these tables to see if they contain a particular email address within their content fields.

I am able to identify the tables and columns that need to be searched with this query:

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id
WHERE
  sys.columns.name LIKE '%content%'
  AND (sys.columns.max_length = -1 OR sys.columns.max_length > 100)

The above query returns 86 tables/columns that should be searched. I am able to manually query those tables to check if the respective columns contain the email address of concern. Here's an example:

SELECT *
FROM TableContainingContent
WHERE SomeContent LIKE '%@mail-domain-of-concern.com%' OR SomeContent LIKE '%mailto:%'

I want to avoid manually making 86 of these queries. My first thought is to pull the list of tables/columns from the first query into a spreadsheet to ease the process of manually forming these queries. Although, it would be great to find a way to dynamically query all of these tables/columns using something like sp_executesql, as the number of tables/columns changes daily.

Any thoughts on the best way to accomplish this would be greatly appreciated.

UPDATE: This is different from the similar question posted in the comments. I'm not looking to search all tables, only the tables containing columns that match the specified criteria.

3+ part naming for columns is deprecated, and should be avoided. It will stop working on day. — Thom A, Nov 21 '19 at 18:15
*the number of tables/columns changes daily* - really? If my DB schema changed daily i'd be quite worried — Caius Jard, Nov 21 '19 at 18:17
@CaiusJard - Yes. The admin end users are able to "configure" this CMS using a UI that inevitably adds more tables/columns. — Tom Faltesek, Nov 21 '19 at 18:30
@Larnu - Yes. There are tables that contain multiple *content* fields. — Tom Faltesek, Nov 21 '19 at 18:31
Does this answer your question? [Find a string by searching all tables in SQL Server Management Studio 2008](https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008) — SMor, Nov 21 '19 at 18:35

score 1 · Answer 1 · answered Nov 21 '19 at 18:14

How about this:

SELECT
  REPLACE(REPLACE(REPLACE(
    'SELECT "{t}.{c}" as whereisit, * FROM [{t}] WHERE [{c}] LIKE "%@mail-domain-of-concern.com%" OR [{c}] LIKE "%mailto:%";'
    ,'{t}', t.name)
    ,'{c}', c.name)
    ,'"', '''')
FROM
  sys.columns c JOIN sys.tables t ON c.object_id = t.object_id
WHERE
  c.name LIKE '%content%'
  AND (c.max_length = -1 OR c.max_length > 100)

If you run it it will generate 85 select statements into the grid. Copy the grid, paste into a new query window and run

It's the SQL equivalent of what you were going to do in Excel. You could look at turning it into an executable string and exec it.. Not sure I would though

This is great. @Larnu was able to provide a solution that avoids me having to copy/paste into excel. — Tom Faltesek, Nov 21 '19 at 18:47

score 1 · Accepted Answer · answered Nov 21 '19 at 18:26

I haven't tested this, and I assume that multiple columns could have a name like content in a single table:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF +
                         N'SELECT *' + @CRLF +
                         N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + @CRLF +
                         N'WHERE ' + 
                         STUFF((SELECT @CRLF +
                                       N'   OR ' + QUOTENAME(c.[name]) + N' LIKE ''%@mail-domain-of-concern.com%'' OR ' + QUOTENAME(c.[name]) + N' LIKE ''%mailto:%'''
                                FROM sys.columns c
                                WHERE c.object_id = t.object_id
                                  AND c.[name] LIKE '%content%'
                                  AND (c.max_length = -1 OR c.max_length > 100)
                                FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,8,N'') + N';'
                  FROM sys.tables t 
                       JOIN sys.schemas s ON t.schema_id = s.schema_id
                  WHERE EXISTS(SELECT 1
                               FROM sys.columns c
                               WHERE c.object_id = t.object_id
                                 AND c.[name] LIKE '%content%'
                                 AND (c.max_length = -1 OR c.max_length > 100))
                  FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,2,N'')

--PRINT @SQL; --Your best friend. Use SELECT if over 4,000 characters

EXEC sp_executesql @SQL;

Use your best friend to debug syntax errors, as I can't generate a statement.

Edit: This does appear to work: db<>fiddle

Yes -- This works great. Thanks for saving me countless hours. — Tom Faltesek, Nov 21 '19 at 18:48

score 0 · Answer 3 · answered Nov 21 '19 at 18:15

0

You can first create the query string for those 86 columns using the metadata tables and then run the query in one go.

select 'select * from '+table_name+' where '+ column_name +' like ''%@mail-domain-of-concern.com%'' OR ' + column_name +' like ''%mailto:%'''
  from information_schema.tables
where (<your first query>)

answered Nov 21 '19 at 18:15

George Joseph

5,842
10
24

Careful here; this won't work with poorly named objects. – Thom A Nov 21 '19 at 18:15

Dynamically Query All SQL Tables Matching Certain Criteria

3 Answers3