SQL Script to find all Date/time columns in database

select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

Find last modified date of stored procedure or function in SQL server

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P' order by modify_date desc

Delete all Tables based on creation date

--Delete all Tables based on creation date



 DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR FOR
SELECT name AS tname
FROM sys.objects
WHERE create_date < GETDATE() - 1-- Days old

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @sql = 'DROP TABLE ' + @tname
       --EXEC (@sql)  -- For Executing
       PRINT @sql --For Printing

       FETCH NEXT FROM db_cursor INTO @tname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Script to find date and details when database was restored in SQL Server

USE msdb;

SELECT DBRestored = destination_database_name
 ,RestoreDate = restore_date
 ,SourceDB = b.database_name
 ,SourceFile = physical_name
 ,BackupDate = backup_start_date
FROM RestoreHistory h
INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate