query multiple sql tables and alert last record written datetime

Question

query multiple sql tables and alert last record written datetime

Bruin

SSCrazy

Points: 2058
More actions
February 1, 2023 at 2:19 pm

#4144221

I'm trying to develop a script that connects to SQL pulls in a list of tables from a database and query on a single field called
DateStored which is a part of each of tables, and send an email of the Table in list and the latest DateStored value.
Datestored - Datetime
Sample Output:
Table - xxx Last Record Written:Datestored
Table - xyz Last Record Written:Datestored
Thanks looking for some examples

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

You've posted this in the Powershell forum, so should we be assuming you want a solution that uses Powershell, rather than T-SQL?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 2

Be good to have some sample data and sample results here, also what have you tried?

Bruin SSCrazy Points: 2058 More actions · Answer 3

yeah thinking the script in PS so if needed updated adding more tables to list would be easy to update script rather than having someone having to use SSMS to update the SP.

Connection Stuff:

DBserver:Fphost

DBName:TcProd

Table List : BOL,SOA,ProdAcct

Field to Check: DateReported(DateTime)

Output to Email:

Table:BOL Last updated: DateReported --> Max(DateReported)

Table:SOA Last updated: DateReporte -> Max(DateReported)

THanks.

Thom A SSC Guru Points: 99130 More actions · Answer 4

Bruin wrote:

yeah thinking the script in PS so if needed updated adding more tables to list would be easy to update script rather than having someone having to use SSMS to update the SP.

So dynamic SQL isn't an option?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bruin SSCrazy Points: 2058 More actions · Answer 5

Was trying to keep it in script if possible.

Thx.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

It should be pretty straight forward to take a list of table names and build a sql script that can run as a single query to grab the one with the max date in that field. Is your input for that going to be a parameter? or a file with a list? or something else?

Thom A SSC Guru Points: 99130 More actions · Answer 7

If you can do this in T-SQL, rather than Powershell, this isn't particularly difficult if I am honest. I use a column name that appears often in one of my databases for the value of @ColumnName, but you would obviously change this for your environment:

DECLARE @ColumnName sysname = N'History@';

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

DECLARE @Delimiter nvarchar(20) = @CRLF + N'UNION ALL' + @CRLF;

SELECT @SQL = STRING_AGG(CONVERT(nvarchar(MAX),'') +
                         N'SELECT N' + QUOTENAME(s.[name],'''') + N' AS SchemaName,' + @CRLF +
                         N'       N' + QUOTENAME(t.[name],'''') + N' AS TableName,' + @CRLF +
                         N'       MAX(' + QUOTENAME(@ColumnName) + N') AS ' + QUOTENAME(CONCAT(N'MAX',@ColumnName)) + @CRLF +
                         N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]), @Delimiter) + N';'
FROM sys.schemas s
     JOIN sys.tables t ON s.schema_id = t.schema_id
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.[name] = @ColumnName;

--PRINT @SQL;
EXEC sys.sp_executesql @SQL;

I also assume you're on a recent version of SQL Server, and thus have access to STRING_AGG. If not, you'll need to use a different way to aggregate the string, such as the "old" FOR XML PATH (and STUFF) method.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bruin SSCrazy Points: 2058 More actions · Answer 8

Bruin

SSCrazy

Points: 2058

February 1, 2023 at 5:28 pm

#4144311

I would use a File as a list.

Thx.

Bruin SSCrazy Points: 2058 More actions · Answer 9

Bruin

SSCrazy

Points: 2058

February 1, 2023 at 5:29 pm

#4144312

This DB is running on a SQL2012 box

Bruin SSCrazy Points: 2058 More actions · Answer 10

Bruin

SSCrazy

Points: 2058

February 1, 2023 at 5:30 pm

#4144313

Thanks Thom for the SQL piece.

Thom A SSC Guru Points: 99130 More actions · Answer 11

Bruin wrote:

This DB is running on a SQL2012 box

I will be honest, I assumed you wouldn't be using an unsupported version of SQL Server, as you didn't state you were. You can still use the solution I provided, but (as mentioned) you'll need to switch from STRING_AGG, such as the previously mentioned FOR XML PATH (and STUFF) method.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 12

Bruin wrote:

I would use a File as a list.
Thx.

So where does this file get the list of tables with the tables that has the column you want to query? How do you ensure that the file is updated when a new table is created, or when a table has the column you need added to it (when it didn't previously)?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Bruin SSCrazy Points: 2058 More actions · Answer 13

I would manually update the file from a folder, and this list of tables won't change. The alert will run daily maybe every x hours..

We had an issue where table hadn't been updated in 3 days and caused problems this would be a stop gap to trying to figure out why updates didn't happen.

Thanks.

Phil Parkin SSC Guru Points: 246970 More actions · Answer 14

Bruin wrote:

I would manually update the file from a folder, and this list of tables won't change. The alert will run daily maybe every x hours..
We had an issue where table hadn't been updated in 3 days and caused problems this would be a stop gap to trying to figure out why updates didn't happen.
Thanks.

As this is a stop-gap solution with an unchanging set of parameters, why are you trying to come up with such an elaborate solution? You could set up a SQL Agent job to do this. No PowerShell needed.

You state above that the list of tables won't change. But your earlier post stated the following:

... so if needed updated adding more tables to list would be easy to update script rather than having someone having to use SSMS to update the SP.

I might be missing something, but these seem contradictory to me.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel