SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Date when a column is added to table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

REDDY
Starting Member

43 Posts

Posted - 11/13/2003 :  12:07:11  Show Profile  Reply with Quote
Is there any way to find the date, when a perticular column is added to a table??

Thanks in advance

Reddy

nic
Posting Yak Master

209 Posts

Posted - 11/13/2003 :  12:21:44  Show Profile  Reply with Quote
Do you mean a new row is added? If so you could create a trigger that adds a datatime stamp on an insert.

If you really mean when a column is added.... sorry no idea, maybe someone else can help

Nic
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/13/2003 :  12:40:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
Got Log Explorer?

I don't think that there is a system table that would have this information. Syscolumns does not have a datetime stamp, sysobjects does but that would only help you if they ran DROP and CREATE on the table to get the column added somewhere other than at the end.

Why do you need to find this out though? Don't you have control over your environment? If not, sounds like it is time to do that.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/13/2003 :  12:42:17  Show Profile  Reply with Quote
I don't think so...

You can for tables,,

Check out:

SELECT * FROM syscolumns

SELECT * FROM sysobjects



Brett

8-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 11/13/2003 :  12:51:34  Show Profile  Reply with Quote
Isn't there a stored procedure that allows you to put comments associated with an object? Or is that only for tables...(Or was that Oracle?)



Brett

8-)
Go to Top of Page

REDDY
Starting Member

43 Posts

Posted - 11/13/2003 :  13:47:11  Show Profile  Reply with Quote
Thanks a lot for all your replies.

I need the date, perticular column is added to a table

Unfortunately We do not have any third party sql tools,all we depend on microsfot tools only.

My boss posed this question to me this morning .He want's to track some other information based on this.

Microsoft should have provided this basic information in system tables.

I am backing up the scripts daily,so I can search the files and can find but looking for any other easy way.

thanks for all your inputs.

Regards
Reddy
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/13/2003 :  13:50:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
Can't you just ask the DBA when a column was added? The DBA should have this information in VSS or something like that.

Tara
Go to Top of Page

REDDY
Starting Member

43 Posts

Posted - 11/13/2003 :  14:04:17  Show Profile  Reply with Quote
TARA
I am the DBA ...My Boss(product Manager) added that column without my notice and he forgot when exactly he added that column,but the question is in general is there a way to find the column added date to table using any system tables or stored procedures or undocumented stored procedures???

thanks
Reddy

Edited by - REDDY on 11/13/2003 14:05:36
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 11/13/2003 :  14:11:14  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well, if you've got the transaction log then you could use DBCC LOGINFO to read the transaction log. This is not easy. I don't know how to use the command, but there have been some examples of it here so search the forums for DBCC LOGINFO.

Why does a product manager have the ability to make schema changes? We limit the access to DBAs and developers (developers can only make these changes in development though).

If your boss added the column by dropping the table and then recreating it, you can use the crdate column in sysobjects for that table to figure out when it was done. He probably used EM though and doesn't know what command was run.

Tara
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 11/13/2003 :  20:25:17  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
I am the DBA ...My Boss(product Manager) added that column without my notice and he forgot when exactly he added that column
So your boss is asking YOU to find out when HE did something that HE forgot and YOU had NO knowledge of in the first place...

Assuming you cannot run away from this job (seriously think about it), I urge you to revoke every last right he has to your database server. Next time he's gonna ask you to recover a database he accidentally dropped but can't remember when, but he's pretty sure he deleted all the backup files too, just to save disk space.
Go to Top of Page

REDDY
Starting Member

43 Posts

Posted - 11/14/2003 :  11:17:02  Show Profile  Reply with Quote
Thanks a lot for all your suggestions and support,

I guess the actual topic is diverted a bit ,Administrative issues are totally internal and sensitive which cannot
be solved by forums,so no point extending the topic on these issues.

Comming to the acual point,based on your postings I came to a conclusion that there is no direct way to find the
Date when a column is added to table.

Thanks
Reddy
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000