SQL Statement That Will Get Table Schema And Created Date

Question

I am trying to create a SQL Statement that will return the table schema name and the if the data was created a day ago.

This will create the SQL Statement with the table schema name:

DECLARE @SqlStatement VARCHAR(MAX)
SELECT @SqlStatement = 
    COALESCE(@SqlStatement, '') + 'DROP TABLE [TMP].' + QUOTENAME(TABLE_NAME) + ';' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TMP'

PRINT @SqlStatement

How do I add in the where clause if the table was created a day ago all in the same statement?

Answer 1

The create_date field is stored in sys.tables. You can then join back to sys.schemas to get the schema name.

Something like this:

declare @SqlStatement varchar(max)
select @SqlStatement = COALESCE(@SqlStatement, '') + 'DROP TABLE [TMP].' + QUOTENAME(t.name) + ';' + CHAR(13)
from sys.tables t 
    join sys.schemas s on t.schema_id = s.schema_id 
where s.name = 'TMP'
    and t.create_date > dateadd(day,-1,getdate())
print @SqlStatement