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?
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?
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.
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.
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'
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'