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).
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