Post reply

Inception Dates

  • September 15, 2011 at 12:48 pm

    #243856

    I have been tasked with finding inception dates for about 20 DB's. (date we started using a DB)

    Problem is I can't use the 'date created' because the DB may have been moved during a hardware upgrade.

    So I was hoping there was some way of finding this out without having to blindly search thru tables looking for date fields and then finding the min(date).

    Any ideas?

  • September 15, 2011 at 1:01 pm

    #1382228

    I ran a trusty Google search and came up with the following, hope it helps.

    http://sqlblog.com/blogs/michael_zilberstein/archive/2008/03/18/5629.aspx

    Actually this snippit is much cleaner and easier.

    SELECT name,crdate

    FROM master..sysdatabases

    Chris Powell

    George: You're kidding.
    Elroy: Nope.
    George: Then lie to me and say you're kidding.

  • September 15, 2011 at 1:19 pm

    #1382233

    I saw those also..

    they don't give me the correct results...maybe it's because we transferred old data into the system.

    (I think) I need a way to find all date fields in a db then the min date.

  • September 15, 2011 at 1:47 pm

    #1382239

    using this query gives me the correct listing of all the date fields in my DB.

    select tbl.name as 'Table', c.name

    as 'Column Name', t.name as 'Type'

    from sys.columns as c inner join sys.tables as tbl on tbl.object_id = c.object_id inner join sys.types as t on c.system_type_id = t.system_type_id where t.name in ('datetime', 'date') order by c.name

    now how can I get the vaules from all date fields and then get the min date?

  • September 15, 2011 at 2:33 pm

    #1382266

    This will give you a script you can run against each database. It will return you the min date as the first column and the table and column names as the second column.

    select 'select min(' + c.name + ') as [MinDate],' + CHAR(39) + tbl.name +'.'+ c.name + CHAR(39) + ' as [Table.Column] from '+ tbl.name + '

    union all'

    from sys.columns as c inner join sys.tables as tbl on tbl.object_id = c.object_id

    inner join sys.types as t on c.system_type_id = t.system_type_id

    where t.name in ('datetime', 'date') order by c.name

    The output looks like this:

    select min(agent_start_date) as [MinDate],'syssessions.agent_start_date' as [Table.Column] from syssessions

    union all

    select min(backup_finish_date) as [MinDate],'backupset.backup_finish_date' as [Table.Column] from backupset

    union all

    select min(backup_start_date) as [MinDate],'backupset.backup_start_date' as [Table.Column] from backupset

    With a final output like this:

    MinDate Table.Column

    ------------------------- ----------------------------

    2011-08-29 08:22:09.837 syssessions.agent_start_date

    2011-07-15 13:11:13.000 backupset.backup_finish_date

    2011-07-15 13:11:12.000 backupset.backup_start_date

    Note you must remove the last "union all"

    This however may not help you as you can get DoBs which may be very old.

    Cheers

    Leo

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • September 15, 2011 at 2:55 pm

    #1382280

    AWESOME

    Thanks leo!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply