Script to get max dates on tables that contain a certain column.

IF OBJECT_ID('tempdb..#Tables') is not null
begin
 drop table #Tables
end

Create Table #Tables
(
 idx int IDENTITY(1,1),
 TableName varchar(100)
)

IF OBJECT_ID('tempdb..#Result') is not null
begin
 drop table #Result
end

Declare @DateColumn varchar(100),
@TablesFetchSql nvarchar(max)

/*Replace this with appropriate Date column.*/SET @DateColumn='EffectiveDate'


Create Table #Result
(
 TableName varchar(1000),
 MaxDate datetime,
 SqlStatement varchar(1000)

)

SET @TablesFetchSql=N'
 select ss.[name]+''.''+so.[name] 
   
   from DBName.sys.tables so
join
     DBName.sys.schemas ss on so.schema_id=ss.schema_id 
     where so.object_id in
   (
     select id from DBName.dbo.syscolumns  where [name]=''@DateColumn''
   )'

Select @TablesFetchSql=replace(@TablesFetchSql,'DBName',db_name())
Select @TablesFetchSql=replace(@TablesFetchSql,'@DateColumn',@DateColumn)

--SELECT @TablesFetchSql
insert #Tables
execute sp_executesql @TablesFetchSql


Declare @idx int,
@ubound int,
@sqlcmd nvarchar(1000),
@TableName varchar(1000)

set @idx=1
Select @ubound=(select max(idx) from #Tables)

while @idx<=@ubound
begin
 Select @TableName=TableName from #Tables where idx=@idx

set @sqlcmd=N' select max('+@DateColumn+') Max'+@DateColumn+','''+@Tablename+''' TableName from '+db_name()+'.'+@TableName+'(NOLOCK)' 

insert #Result
(
 MaxDate,
 TableName
)
exec (@sqlcmd)
Update #Result set SqlStatement=@sqlcmd where TableName=@Tablename
SET @idx=@idx+1
end

/*End Script*/
/*Output*/ 
select * from #Result