How to update the Created Date on a table in SQL 2005 ?

Question

Post reply

How to update the Created Date on a table in SQL 2005 ?

theflyingdutchman

SSC Enthusiast

Points: 186
More actions
April 4, 2008 at 7:54 pm

#120845

Hello,
In SQL 2000, I was able to issue a SQL statement like this to update the Create Date:
update sysobjects
set crdate = getdate()
where name = 'CUSTOMER'
How would I issue a similar command in SQL 2005?

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

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

N J Right there with Babe Points: 732 More actions · Answer 1

In no way you can update system table in sql server 2005.

NJ

Cory E. SSCertifiable Points: 7076 More actions · Answer 2

you could try renaming the table, create a new one, and copy the data back. i dont think I would recommend, but that is an option

-- Cory

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

You could also maintain a separate table with all of your table names and the other pertinent information you need. Then set up DDL triggers on each of your tables, so you can capture what change occurred and when it occurred.

You can then reference this table to determine when the table was last modified.

My blog: http://jahaines.blogspot.com

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 4

Although it is not recommended but still if you want to change value in system table. You may do it by starting sql server using -f switch.

like:

sqlservr -f

[font="Verdana"]--www.sqlvillage.com[/size][/font]

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 5

Updating system tables in SQL 2005 is possible if you login as Dedicated adminstrator!! DAC has the privilege to update system table. Refer the below link for DAC.

Dedicated Administrator Connection

http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Regards..Vidhya Sagar
SQL-Articles

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

This is the second time this same question has been asked... and just like I said on that identical post, directly updating system tables is a form of "Death by SQL".

Just don't do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)