Knowing system table value changes

Question

Knowing system table value changes

Pravin Patel-491467

Say Hey Kid

Points: 674
More actions
September 18, 2007 at 7:42 am

#174812

Dear Friends....
How can i know that if i change any table structure. is there any other effect is occure in any system table thet field value is change if i m do any changes into table structure????
If the table is exist in sql server 2005 in which any table structure changes is effeted then please give that system table with column name.

Thanks.........

Viewing 9 posts - 1 through 8 (of 8 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Hi Pravin,

have a look at the sys.tables system view. It contains a modify_date column (it is inherited from sys.objects, which has this information for all schema objects).

You can read more on sys.tables on http://msdn2.microsoft.com/en-us/library/ms187406.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 2

SQL Server 2005 has the ability to track table changes, if you create the appropriate triggers. Then you will know what has changed and when. You can find info on this at microsoft web sites, books online, and a few other SQL Server websites have articles on it.

Here is a link From SQLServerCentral on the subject http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes

Assuming I am understanding your post

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

DDL triggers are indeed nice, but it is rather common to disable all of them before certain changes So while they are really useful, it is important to keep in mind that they not always show all the modifications. They are good however for detecting non-malicious changes.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 4

JDixon-385826

SSCrazy

Points: 2723

September 18, 2007 at 10:18 am

#734359

good to know. Thanks

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 5

Hi Andras:

Thanks for your reply.

In SQL 2000 we had a field called "schema_ver" in sysobjects which changes whenever any alteration occurs to any object. Is there any equivalent system object field in SQL 2005 which is equivalent to schema_ver?

Thanks again!

Pravin

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 6

Hi Andras:

Thanks for your reply.

In SQL 2000 we had a field called "schema_ver" in sysobjects which changes whenever any alteration occurs to any object. Is there any equivalent system object field in SQL 2005 which is equivalent to schema_ver?

Thanks again!

Pravin

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

Hi Pravin,

As far as I know there is no equivalent to schema_ver in 2005. The only thing that is similar is the modify_date of the sys.objects table I mentioned above.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Pravin Patel-491467 Say Hey Kid Points: 674 More actions · Answer 8

Pravin Patel-491467

Say Hey Kid

Points: 674

September 25, 2007 at 3:24 am

#735932

Thank you....... Andras