Table Information Display

We are migrating (finally!!! :rolleyes: ) 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?

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.

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?

You need right click the table then open properties to get those. Query sys.sysobjects to get creation and modify date.

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 :) )

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. :eek:

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.

Quote:

---

Originally Posted by skhanal

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?

Does db have that table?

Yes it does have that table.

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.

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'

---

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 [ _ ]?

After I took the screen shot, I erased the table name as well as any reference to the actual database name.

Do you see the table when you do not have the WHERE clause?

Yes...in both 2000 and 2008 (although without the WHERE clause I get the create dates for every table in my database, which I don't need)

As an FYI: This table is updated weekly as the result of a DTS package (and yes, I know, I need to redo it into an SSIS package – haven’t got there yet though).

The DTS package drops the existing table, recreates it, and then imports this hue-mung-i excel spreadsheet into the newly recreated SQL Server table.

In my program, I have a screen which allows the users to selected fields from this table in order to perform ad hoc queries which gives them raw data dumps.

I have the create date displayed in the window caption. This allows the users to know that the data they will be going after is up to date (this is why I need that create date.)

(I just received official confirmation that our migration will be occurring next weekend - yea! :D )

Just to confirm

you get all other information and empty date when you have WHERE name=''
you get all information including date for the able when you do not have WHERE condition

Sounds like a formatting problem on date field. It does not make sense though.

To be completely clear, I am getting the information (create date) for my individual table. However, in order to get the create date I am using this).

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 Table'

---

Below is the way I was doing it, and it returns an empty field in 2008, but still works just fine in 2000.

Code:

---

SELECT crdate
FROM sysobjects
WHERE [name] = 'Name Of Table'

---

It works in my environment, Microsoft SQL Server 2008 (SP1) - 10.0.2531.0

Which version are you using?

Hopefully there isn't anything in here that I shouldn't be posting

Quote:

---

Originally Posted by SQL Server 2008 About Box

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )
Microsoft Analysis Services Client Tools 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.3603
Operating System 5.1.2600

---