how to find a date – in SQL Server

#1 – create table

CREATE TABLE TheDates(
TheDate SMALLDATETIME,
DbName varchar(255),
TblName varchar(255),
ColName varchar(255)
)

#2 – run this in a database

Select ‘insert into TheDates(TheDate, DbName, TblName, ColName) Select DATEADD(day, DATEDIFF(day, 0, [‘ + sc.name + ‘]), 0) as TheDate, ‘ + char(39) + DB_NAME() + char(39) + ‘ as DbName, ‘ + char(39) + so.name + char(39) + ‘ as TblName, ‘ + char(39) + sc.name + char(39) + ‘ as ColName From [‘ + DB_NAME() + ‘].dbo.[‘ + so.name + ‘] with (nolock) WHERE [‘ + sc.name + ‘] IS NOT NULL’ + CHAR(10) + ‘go’

from sysobjects so
inner join syscolumns sc
on so.id = sc.id
where so.xtype = ‘u’
and TYPE_NAME(sc.xtype) in (‘smalldatetime’, ‘datetime’)
AND so.name not like ‘MS%’

#3 – take the output from step#2 and re-run it in a new database window.

 

Now you will be able to have a single date table that lists every date in your database.

This is a work-around for when I have a MASSIVE number of columns, for example, I’m doing this on ~850 columns right now, it should just take about 5 minutes

I’m basically trying to determine.. which tables / date columns got a LARGE number of new rows / values today.. Because I just ran a bunch of ETL jobs

-Aaron