MaxDate for all Tables

Transact-SQL
1

Hi,
i want to write the max(Orderdate) from all Tables in DB into a #TempTable.
How can i realise this in a easy way ? (All Tables in the DB have 1 Col. with Orderdate).

CREATE TABLE #TempTbl(
[tblName] nvarchar NULL,
[lastOrderDate] [datetime] NULL)

Thanks
Regards
Nicole

2

Try this:

declare @sql nvarchar(max)=stuff((select ' union all select '''+schema_name(a.[schema_id])+'.'+a.[name]+'.'+b.[name]+''' as tablename,max(orderdate) as orderdate from '
                                        +schema_name(a.[schema_id])+'.'+a.[name]--+'.'+b.[name]
                                    from sys.tables as a
                                         inner join sys.columns as b
                                                 on b.[object_id]=a.[object_id]
                                                and b.[name]='orderdate'
                                   where a.[type]='U'
                                     for xml path('')
                                            ,type
                                 ).value('.','nvarchar(max)')
                                ,1,11,''
                                )
;

execute ('insert into #temptbl(tblname,lastorderdate) '+@sql);

select * from #temptbl;