How to select date columns from every table in the database?

Question

I'm trying to select the minimum date from every date column inside my database. What I mean with minimum is that I want to obtain the earliest date.

From what I've been investigating, it seems that the best approach is to use dynamic SQL. This is the code I'm currently trying but I can't get anything out of it.

DECLARE @Sql nvarchar(max) 

SELECT @Sql = STUFF(
        (       
            SELECT ' UNION ALL SELECT '''+ MIN(C.COLUMN_NAME) +','+''''+ C.TABLE_NAME +''' As TableName FROM '+ C.TABLE_SCHEMA+ '.' +C.TABLE_NAME
            FROM Information_schema.Columns C
            JOIN Information_schema.Tables T
            ON C.TABLE_NAME = T.TABLE_NAME
            WHERE C.DATA_TYPE in ('datetime','date','datetime2','timestamp','time')
                and C.TABLE_SCHEMA NOT IN ('STG','HIST')
                and C.TABLE_SCHEMA IN ('COSTOS')
                and T.TABLE_TYPE <> 'VIEW'
            GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME
            FOR XML PATH('')
        ), 1, 11, '')

EXEC(@Sql)
SELECT @Sql 

I'm not familiar with dynamic SQL and the output of this query is a null result.

Answer 1

This is based on your attempt, one thing you need to do is to put MIN() inside the statement:

DECLARE @sql nvarchar(max) 
DECLARE @err int

SELECT @sql = STUFF(
    (
    SELECT 
        'UNION ALL SELECT ' +
        QUOTENAME(C.TABLE_NAME, '''') + ' AS [TableName], ' +
        QUOTENAME(C.TABLE_SCHEMA, '''') + ' AS [SchemaName], ' +
        QUOTENAME(C.COLUMN_NAME, '''') + ' AS [ColumnName], ' +
        'MIN(' + QUOTENAME(C.COLUMN_NAME) + ') AS [ColumnValue] '  +
        'FROM ' + QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME) + ' '
    FROM Information_schema.Columns C
    JOIN Information_schema.Tables T ON C.TABLE_NAME = T.TABLE_NAME
    WHERE 
        C.DATA_TYPE IN ('datetime','date','datetime2','timestamp','time')
        AND C.TABLE_SCHEMA NOT IN ('STG','HIST')
        AND C.TABLE_SCHEMA IN ('COSTOS')
        AND T.TABLE_TYPE <> 'VIEW'
    FOR XML PATH(''), TYPE
    ).value('text()[1]', 'nvarchar(max)'), 1, 10, '')

PRINT @sql
EXEC @err = sp_executesql @sql