Find all tables with a specific column name + where value is lower than X

I need help in searching our database. So we have this problem that we need to know all tables, with the column_name “sysmodified” and see if there are any entries before a specific date (25-sep-2019).

I tried to find the answer on google and stackoverflow, but I either get an answer how to get the results before 25-sep within 1 table Example1, or results how to get all tables, which has this column_name Example2.

Using the code I have so far (see below), we know that there are 325 tables, which contain the column_name “sysmodified”. I could manually use example 1 to get my information, but I was hoping for a way to get the results that I need with just one query.

This is what I have so far:

USE [database2]
GO
SELECT t.name AS table_name, 
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%sysmodified%'
ORDER BY schema_name, table_name;

However if I try to enter anything like sysmodified < ‘20190925’. I get errors

WHERE c.name LIKE '%sysmodified%'
AND t.sysmodified < '20190925'

or this approach, which also results in errors

SELECT t.name AS table_name, sysmodified,

based on (but I cannot add 325 columnnames in FROM?)

SELECT
  title,
  primary_author,
  published_date
FROM
  books
WHERE
  title LIKE 'The%'

Hopefully someone could help me with an approach how to tackle this problem. We use Microsoft SQL Server Management Studio 17 (if that might be relevant).

Answer

This is fairly simple dynamic sql to put together. This should produce the results you are looking for as I understand your requirements.

declare @SQL nvarchar(MAX) = ''

select @SQL = @SQL + 'select distinct TableName = ''' + object_name(object_ID) + ''' from ' + quotename(object_name(object_ID)) + ' where ' + quotename(c.name) + ' < ''20190925'' UNION ALL '
from sys.columns c
where name like '%sysmodified%'

set @SQL = left(@SQL, len(@SQL) - 10) --removes the final UNION ALL

select @SQL
--once you are comfortable that the dynamic sql is correct just uncomment the next line
--exec sp_executesql @SQL