Row insert timestamp?

Just a quick question: Does SQL server 2000 store any identity information for tables in terms of the time a row gets inserted or are you forced to track this yourself? Thanks!Track it yourself

USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) PRIMARY KEY
, Col2 Char(1)
, Col3 datetime DEFAULT getdate()
)
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!|||Originally posted by blm14_cu
But but! I'm lazy... Can't microsoft just do it for me!? ;) Oh well guess I'll just have to add a trigger or something. Thanks!

Did you try my code?

And lazy is good...|||Here, Here's lazy...

uncomment the EXEC to exeute the statements...

But

BE CAREFUL

You can't easily get rid of a column once you add it....|||ooops..forgot the code

Use Northwind
GO

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99|||Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.|||Originally posted by blindman
Can't easily get rid of a column?

Brett's solution is the simplest if you just want to record the insert date on a record. If you need to monitor updates then you will have to write a trigger. I use the CHECKSUM functions verify dirty data.

Yo blind dude...

Want to post how you get rid of a column?|||I use ALTER TABLE DROP COLUMN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) myself. You can do it in Enterprise Mangler too.

-PatP|||Being one of the Lazy DBAs, I do it directly from Enterprise Mangler or have EM generate a script that I can edit.|||What a scrub...there's my DB2 Roots showing..

Thanks guys

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP CONSTRAINT ['+TABLE_NAME+'_myDateCol_Default]'
FROM INFORMATION_SCHEMA.TABLES
GO

SELECT 'ALTER TABLE ['+TABLE_NAME+'] DROP COLUMN [myDateCol]'
FROM INFORMATION_SCHEMA.TABLES
GO

Oh and the original code is wrong

Use Northwind
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT 'ALTER TABLE ['+TABLE_NAME
+'] ADD myDateCol datetime CONSTRAINT '
+'['+TABLE_NAME+'_myDateCol_Default] DEFAULT GETDATE() NOT NULL'
FROM INFORMATION_SCHEMA.TABLES

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL
-- EXEC(@.SQL)
FETCH NEXT FROM myCursor99 INTO @.SQL
END

CLOSE myCursor99
DEALLOCATE myCursor99

SET NOCOUNT OFF

sheesh...what a scrub....|||Oh dear, Brett...

I guess, on the bright side, your work just got a lot easier.|||Funny thing is, I've never had a need to drop a column....

Anyone?

Usually it's part of massive reengineering effort...