Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Table Information Display

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    Table Information Display

    We are migrating (finally!!! ) from SQL Server 2000 to SQL Server 2008 - I have Management Studio on my machine, and I'm starting to play around with it to get comfortable before the actual migration occurs.

    Under Enterprise Manager for 2000 and earlier, when I click on the "Tables" icon in the left hand pane, the table names, owner, type and Create Date are table headers for the tables display in the right hand pane.

    Although the table names are displayed in the left hand pane under Management Studio, the only thing I get in the right hand pane is a blank spot to run some queries.

    Is the SQL Server 2000 right hand pane view no longer available in 2008?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Things have moved around in 2005 and 2008 is similar. Look for Object Explorer Details button on tool bar, this is the closed you have from 2000.

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    Thanks...I found Explorer Details...didn't really give me what I wanted, but I can live with that. However, when I ran this to get the date a table was created, it just came back blank
    Code:
    SELECT crdate 
    FROM sysobjects 
    WHERE [name] = 'Name Of Table'
    Programmatically speaking, how do I get the create date of a 2008 table?

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You need right click the table then open properties to get those. Query sys.sysobjects to get creation and modify date.

  5. #5
    Join Date
    Dec 2009
    Posts
    79
    Actually, I need to programmatically get the table create date returned by that SELECT query.

    I need to programmatically run that SELECT in 3 different VB6 projects, and 7 different VB.NET projects.

    The individuals seeing the table create date displayed on screen will, in all likelyhood, never even know what Management Studio is (at the moment, I can tell you with complete confidence, that they have no idea what Enterprise Manager is )

  6. #6
    Join Date
    Dec 2009
    Posts
    79
    Ok, after some Googling and playing around I came up with this which seems to work in both 2000 (as I need it to until the migration) as well as 2008 (as I need it to after the migration).
    Code:
    SELECT so.[name] AS [TableName], 
    so.[crdate] AS [CreatedDate] 
    FROM information_schema.tables AS ist,
    sysobjects AS so  
    WHERE ist.[table_name] = so.[name]
    AND so.[name] = 'Name Of The Table You Need The Create Date For'
    Is this it, or is there an easier way?

    It seems 6 lines of code is a bit silly when they are replacing 3 lines of code
    Code:
    SELECT crdate 
    FROM sysobjects 
    WHERE [name] = 'Name Of Table'
    Which, actually, could, if you didn't care about readibility, be run from one line.

    I wonder what other interesting "Gotchas" there will be.
    Last edited by Moe1950; 05-24-2010 at 09:05 AM.

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    You don't need to query information_schema view, the information is still in sys.sysobjects table. Old sysobjects is a view now and is there for backward compatibility.

  8. #8
    Join Date
    Dec 2009
    Posts
    79
    Quote Originally Posted by skhanal View Post
    You don't need to query information_schema view, the information is still in sys.sysobjects table. Old sysobjects is a view now and is there for backward compatibility.
    Then why does this
    Code:
    SELECT crdate 
    FROM sysobjects 
    WHERE [name] = 'Name Of Table'
    return an empty field?

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Does db have that table?

  10. #10
    Join Date
    Dec 2009
    Posts
    79
    Yes it does have that table.

  11. #11
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Does it return empty field or empty recordset? Check your database context. Also if your database is created with case sensitivity then name case should match.

  12. #12
    Join Date
    Dec 2009
    Posts
    79
    If I run it from Management Studio, it returns and empty field.

    If I run it from my VB6 program, it returns an empty recordset.

    Attached are some visuals.

    2000.jpg is what happens when run from SQL Server 2000 Query Analyser against the table in an SQL Server 2000 database.

    2008.jpg is what happens when the exact same thing is run from Management Studio against the table in an SQL Server 2008 database.

    However, if I run this from Management Studio, or from VB6 code that is connected to a 2008 database, I get the desired result, not an empty field or recordset
    Code:
    SELECT so.[name] AS [TableName], 
    so.[crdate] AS [CreatedDate] 
    FROM information_schema.tables AS ist,
    sysobjects AS so  
    WHERE ist.[table_name] = so.[name]
    AND so.[name] = 'Name Of The Table You Need The Create Date For'
    Attached Images Attached Images
    Last edited by Moe1950; 05-25-2010 at 09:35 AM.

  13. #13
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Do you see the table when you do not have the WHERE clause?

    Also I only see blank space in WHERE clause, did you erase the table name from the query or it is named [ _ ]?

  14. #14
    Join Date
    Dec 2009
    Posts
    79
    After I took the screen shot, I erased the table name as well as any reference to the actual database name.

  15. #15
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Do you see the table when you do not have the WHERE clause?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •