Click here to Skip to main content
13,048,788 members (87,429 online)
Rate this:
 
Please Sign up or sign in to vote.
Hi,

I Have a SQL TABLE called (PRODUCT)

Initially i have create with 2 columns(COL1 int,COL2 varchar(20)) on 01/Jan/13.
Later on i added new column COL3 VARCHAR(20) on 05/Jan/13.But i altered datatype of COL3 as Int on 07/01/13. I want a query for list out the column name ,created date and modified date.


Query out put should be:

Column_name <-->create_date<-->Modifieddate
COL1<--> 01/Jan/13<--> 01/Jan/13
COL2<--> 01/Jan/13<--> 01/Jan/13
COL3<--> 05/Jan/13<--> 07/Jan/13
Posted 24-Jan-13 22:23pm
Updated 24-Jan-13 22:25pm
v2
Comments
Zoltán Zörgő 25-Jan-13 4:36am
   
Not clear: you want to manipulate data based on the table schema manipulation date? Where do you think this date is stored?
MurugesanYadav 25-Jan-13 5:33am
   
we can find modified date & created date of particular table. But i want modified date & created date of each column of particular table.
Zoltán Zörgő 25-Jan-13 5:45am
   
Well, as I know, there is no method to do this in a retrospective manner.
CHill60 25-Jan-13 4:39am
   
I didn't think the create and modify date were stored on syscolumns see http://msdn.microsoft.com/en-us/library/ms176106.aspx

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

As I suspected in 2008 there is no sys table entry for tracking changes to tables in this way - see
http://stackoverflow.com/questions/6344413/sqlserver-row-date-last-modified[^]

If you really want this functionality you'll have to build it yourself using DDL triggers - have a look at
http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes[^]
or
http://www.simple-talk.com/sql/database-administration/auditing-ddl-changes-in-sql-server-databases/[^]
or upgrade / change tools
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 25 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100