September 3, 2015 at 10:18 am
I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.
I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
Hope that makes sense and that someone out there has a script that will clearly save me many hours of grunt. 😎
September 3, 2015 at 10:46 am
Hrothgar (9/3/2015)
I have several databases to deal with, all with + 250 tables. The databases are not identical and do not conform to a specific naming convention for table names. Most but not all tables have a column called "LastUpdated" containing a date/time (obviously). I'd like to be able to find all rows within a whole database (table by table) where the date/time is greater than a specified date/time.I'm looking for a reliable query that will return all the rows in each of the tables but without me having to write hundreds of individual scripts "SELECT * FROM [dbo.xyz] WHERE LastUpdated > '2015-01-01 09:00:00:000'", or have to look through each table first to determine which of them has the LastUpdated field.
Hope that makes sense and that someone out there has a script that will clearly save me many hours of grunt. 😎
this is not an answer, but the question i ask is why?....and once you have the results, what are you intending to next?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 3, 2015 at 11:46 am
Something like the attached.
September 3, 2015 at 12:36 pm
Very busy at work, just now was able to complete the code. I'll go ahead and post it as an alternative. I prefer a "template" approach, so that the overall SQL is -- to me (and maybe only me :-D) -- easier to see and change. Some like this style, some hate it, so I figured I'd give you the choice.
DECLARE @specified_datetime datetime
DECLARE @column_name_pattern varchar(128)
DECLARE @max_rows_to_list int --0/NULL=all; 1+=that specific # of rows.
DECLARE @print_sql bit --print gen'd sql or not
DECLARE @exec_sql bit --exed gen'd sql or not
SET @specified_datetime = DATEADD(DAY, -1, GETDATE())
SET @column_name_pattern = 'LastUpdate%' --or 'LastUpdated'
SET @max_rows_to_list = 5 --to test: chg to 0/null for final run
SET @print_sql = 1
SET @exec_sql = 1 --to test: chg to 1 to actually exec sql rather than just print it
------------------------------------------------------------------------------------------------------------------------
DECLARE @sql_template varchar(8000)
DECLARE @sql varchar(8000)
DECLARE @schema_id int
DECLARE @object_id int
DECLARE @column_name varchar(128)
SET @sql_template =
'SELECT $max_rows$ * ' +
'FROM [$schema$].[$table$] ' +
'WHERE $column$ > ''' + CONVERT(varchar(8), @specified_datetime, 112) + ' ' +
CONVERT(varchar(12), @specified_datetime, 114) + ''''
DECLARE cursor_tables CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT t.schema_id, c.object_id, c.name AS column_name
FROM sys.columns c
INNER JOIN sys.tables t ON
t.object_id = c.object_id
WHERE
c.name LIKE @column_name_pattern AND
EXISTS(
SELECT 1
FROM sys.types ty
WHERE
ty.name IN ('date', 'datetime', 'datetime2') AND
ty.system_type_id = c.system_type_id AND
ty.user_type_id = c.system_type_id
)
OPEN cursor_tables
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_tables INTO @schema_id, @object_id, @column_name
IF @@FETCH_STATUS <> 0
BREAK;
SET @sql = REPLACE(REPLACE(REPLACE(REPLACE(@sql_template,
'$max_rows$', CASE WHEN @max_rows_to_list > 0 THEN 'TOP (' + CAST(@max_rows_to_list AS varchar(10)) + ')' ELSE '' END),
'$schema$', CAST(SCHEMA_NAME(@schema_id) AS varchar(128))),
'$table$', CAST(OBJECT_NAME(@object_id) AS varchar(128))),
'$column$', @column_name)
IF @print_sql = 1
PRINT @sql
IF @exec_sql = 1
EXEC(@sql)
END --WHILE
DEALLOCATE cursor_tables
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2015 at 12:43 pm
ScottPletcher (9/3/2015)
Very busy at work, just now was able to complete the code. I'll go ahead and post it as an alternative. I prefer a "template" approach, so that the overall SQL is -- to me (and maybe only me :-D) -- easier to see and change. Some like this style, some hate it, so I figured I'd give you the choice....
I prefer to use sp_executesql where I can actually use variables and pass data into the query using appropriate data types instead of converting everything to strings to build the dynamic SQL.
Could do that with templates as well.
September 3, 2015 at 12:47 pm
Lynn Pettis (9/3/2015)
ScottPletcher (9/3/2015)
Very busy at work, just now was able to complete the code. I'll go ahead and post it as an alternative. I prefer a "template" approach, so that the overall SQL is -- to me (and maybe only me :-D) -- easier to see and change. Some like this style, some hate it, so I figured I'd give you the choice....
I prefer to use sp_executesql where I can actually use variables and pass data into the query using appropriate data types instead of converting everything to strings to build the dynamic SQL.
Could do that with templates as well.
I will go with constants on these types of things because SQL will sometimes generate better plans for constant values rather than variable-based ones. I also don't see any great danger of injection here. Finally, I'd rather SQL not cache these plans, as they should be one-offs anyway, so purely ad-hoc SQL is in that sense better.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 3, 2015 at 12:58 pm
ScottPletcher (9/3/2015)
Lynn Pettis (9/3/2015)
ScottPletcher (9/3/2015)
Very busy at work, just now was able to complete the code. I'll go ahead and post it as an alternative. I prefer a "template" approach, so that the overall SQL is -- to me (and maybe only me :-D) -- easier to see and change. Some like this style, some hate it, so I figured I'd give you the choice....
I prefer to use sp_executesql where I can actually use variables and pass data into the query using appropriate data types instead of converting everything to strings to build the dynamic SQL.
Could do that with templates as well.
I will go with constants on these types of things because SQL will sometimes generate better plans for constant values rather than variable-based ones. I also don't see any great danger of injection here. Finally, I'd rather SQL not cache these plans, as they should be one-offs anyway, so purely ad-hoc SQL is in that sense better.
Too many times my ad hoc's aren't.
September 4, 2015 at 1:47 am
That's a very good and very helpful solution; very many thanks.
As the results seem to come out in an arbitrary order, or at least in an order that I don't quite understand, is there any way of identifying the table in the results grid? (I know they're shown in the messages tab but that could be fiddly with over 200 tables of results).
September 4, 2015 at 8:19 am
Hrothgar (9/4/2015)
That's a very good and very helpful solution; very many thanks.As the results seem to come out in an arbitrary order, or at least in an order that I don't quite understand, is there any way of identifying the table in the results grid? (I know they're shown in the messages tab but that could be fiddly with over 200 tables of results).
Yes, sure. I should of thought of that myself.
DECLARE @specified_datetime datetime
DECLARE @column_name_pattern varchar(128)
DECLARE @max_rows_to_list int --0/NULL=all; 1+=that specific # of rows.
DECLARE @print_sql bit --print gen'd sql or not
DECLARE @exec_sql bit --exed gen'd sql or not
SET @specified_datetime = DATEADD(DAY, -1, GETDATE())
SET @column_name_pattern = 'LastUpdate%' --or 'LastUpdated'
SET @max_rows_to_list = 5 --to test: chg to 0/null for final run
SET @print_sql = 1
SET @exec_sql = 1 --to test: chg to 1 to actually exec sql rather than just print it
------------------------------------------------------------------------------------------------------------------------
DECLARE @sql_template varchar(8000)
DECLARE @sql varchar(8000)
DECLARE @schema_id int
DECLARE @object_id int
DECLARE @column_name varchar(128)
SET @sql_template =
'SELECT $max_rows$ ''$schema$.$table$'' AS [schema.table], * ' +
'FROM [$schema$].[$table$] ' +
'WHERE $column$ > ''' + CONVERT(varchar(8), @specified_datetime, 112) + ' ' +
CONVERT(varchar(12), @specified_datetime, 114) + ''''
DECLARE cursor_tables CURSOR LOCAL STATIC FORWARD_ONLY FOR
SELECT t.schema_id, c.object_id, c.name AS column_name
FROM sys.columns c
INNER JOIN sys.tables t ON
t.object_id = c.object_id
WHERE
c.name LIKE @column_name_pattern AND
EXISTS(
SELECT 1
FROM sys.types ty
WHERE
ty.name IN ('date', 'datetime', 'datetime2') AND
ty.system_type_id = c.system_type_id AND
ty.user_type_id = c.system_type_id
)
ORDER BY SCHEMA_NAME(t.schema_id), OBJECT_NAME(c.object_id)
OPEN cursor_tables
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_tables INTO @schema_id, @object_id, @column_name
IF @@FETCH_STATUS <> 0
BREAK;
SET @sql = REPLACE(REPLACE(REPLACE(REPLACE(@sql_template,
'$max_rows$', CASE WHEN @max_rows_to_list > 0 THEN 'TOP (' + CAST(@max_rows_to_list AS varchar(10)) + ')' ELSE '' END),
'$schema$', CAST(SCHEMA_NAME(@schema_id) AS varchar(128))),
'$table$', CAST(OBJECT_NAME(@object_id) AS varchar(128))),
'$column$', @column_name)
IF @print_sql = 1
PRINT @sql
IF @exec_sql = 1
EXEC(@sql)
END --WHILE
DEALLOCATE cursor_tables
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 4, 2015 at 8:52 am
Excellent; many thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply