Month: February 2021

Had a situation where needed to get an idea of when a database was last used. Created a quick query to generate select statements for all the date columns in all the user tables in the database.

    SELECT C.[name] "table_name"
         , A.[name] "column_name"
         , B.[name] "column_type"
         , 'union select ''' + C.name + '.' + A.name + ''' "column_name", max([' + A.name + ']) "maxdate" from [' + C.name + ']' "cmd"
      FROM sys.columns A
INNER JOIN sys.types B
        ON A.user_type_id = B.user_type_id
INNER JOIN sys.tables C
        ON A.object_id = C.object_id
     WHERE B.name like '%date%'
  ORDER BY C.name
         , A.name