Post reply

datetime query

  • March 3, 2004 at 11:55 pm

    #162058

    Hi,

    I have a very easy question for you...

    How do I find out which columns in my database have a datetime datatype?

    I have queried a number of system tables, but can find what I'm looking for...

    Thanks.

     

     

  • March 4, 2004 at 2:17 am

    #497175 

    select o.name, c.name
from sysobjects o inner join syscolumns c on c.id = o.id
where o.xtype = 'U' and c.xusertype = 61

  • March 5, 2004 at 12:33 am

    #497470

    select TABLE_NAME,COLUMN_NAME

    from INFORMATION_SCHEMA.COLUMNS

    where DATA_TYPE = 'datetime'

  • March 5, 2004 at 1:20 am

    #497478

    That's fine if you've got INFORMATION_SCHEMA.

    If you have upgraded your database in situ instead of starting from scratch you won't have it, and can't install those tables.  (Can't find the reference for that at the moment!)

  • March 5, 2004 at 7:40 am

    #497546

    I don't have sql 7 here but I thought that information_schema.* where views that extracted system tables information.

    Are you saying that if  I upgrade a db from 7 to 2000 information_schema.* are not availabe?


    * Noel

  • March 5, 2004 at 6:46 pm

    #497689

    My memory must be somewhat hazy!  I managed to relocate the article - thanks to the computer having a better memory than me.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;294350

    The bit about the upgrade is a bit more restrictive than I remembered ...

    If you try to upgrade user databases from SQL Server 7.0 to SQL Server 2000 by using either the Copy Database Wizard or the sp_attach_db stored procedure, the user databases in SQL Server 2000 will not contain the INFORMATION_SCHEMA views. The same applies if you try to upgrade your existing SQL Server 7.0 default instance to SQL Server 2000 using SQL Server 2000 Setup. This behavior is by design.

    Also, note the views are in the "master" database only.  The server that I thought didn't have the views does have them! 

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

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