How to get Max Date Value of Date column in Multiple tables

  • A+
Category:Languages

For example I have 2 tables in Database. Ex : Table T :

declare @t table (name varchar(20),DOB date) Insert into @t (name,DOB) values ('Mohan','2001-07-19') Insert into @t (name,DOB) values ('Minu','1998-06-19') 

Table : TT

declare @tt table (name varchar(20),DOB date) Insert into @tt (name,DOB) values ('Raju','2010-07-19') Insert into @tt (name,DOB) values ('Rani','2001-06-19') 

Now I have a Query to get Table name and column names of multiple tables basing on Date type filter .

SELECT   C.TABLE_SCHEMA, c.TABLE_NAME,c.COLUMN_NAME FROM     INFORMATION_SCHEMA.COLUMNS c           JOIN sys.objects o ON o.name = c.TABLE_NAME    WHERE    o.type = 'U' AND C.DATA_TYPE = 'Datetime' 

Output :

T_Schema    T_name T_column   dbo         T      DOB      dbo         TT     DOB 

But how can I get max Date of both tables like below output :

  T_Schema  T_name T_column  Max_dt   dbo         T      DOB     2001-07-19   dbo         TT     DOB     2010-07-19 

Suggest me the best way to achieve this.

 


SQL to BUILD SQL for you

DECLARE @SQL as nvarchar(max) =''; select @SQL = @SQL + 'SELECT ' + Column_Name + ' adate, ''' + Column_Name + ''' colname, ''' + Table_name + ''' tabname  FROM ' +  Table_name + ' UNION ' FROM   INFORMATION_SCHEMA.COLUMNS where data_type like '%date%' select @SQL = 'SELECT TOP 100 * FROM (' + LEFT(@SQL, LEN(@SQL) -6) + ') IQ  WHERE IQ.adate IS NOT null ORDER BY IQ.adate DESC';   --cut n paste the sql below, see what it does for you select @SQL 

mark II - executes sql for you, and sorts out names with spaces in them

DECLARE @SQL as nvarchar(max) =''; select @SQL = @SQL + 'SELECT [' + Column_Name + '] adate, ''' + Column_Name + ''' colname, ''' + Table_name + ''' tabname  FROM [' +  Table_name + '] UNION ' FROM   INFORMATION_SCHEMA.COLUMNS where data_type like '%date%' select @SQL = 'SELECT TOP 100 * FROM (' + LEFT(@SQL, LEN(@SQL) -6) + ') IQ  WHERE IQ.adate IS NOT null ORDER BY IQ.adate DESC';  select @SQL; EXEC sp_executesql @sql; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: