Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find out the modified table and its column details Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 6:56 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
hi,

i have to find out the modified table and its column details .

ie,if we are executing a query.It will insert or update records .

i have to get tables and column (which column , table changed ,updated or inserted value,previous value in case of exiting record etc)


is there any sql script or tools or any feature available??


if Any method .. please let me know


thank you
Post #1600599
Posted Thursday, August 7, 2014 7:18 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 13,455, Visits: 12,318
sumith1andonly1 (8/7/2014)
hi,

i have to find out the modified table and its column details .

ie,if we are executing a query.It will insert or update records .

i have to get tables and column (which column , table changed ,updated or inserted value,previous value in case of exiting record etc)


is there any sql script or tools or any feature available??


if Any method .. please let me know


thank you


Not quite sure what context you are looking for but perhaps a trigger? Or you might like into CDC (Change Data Capture).


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1600610
Posted Thursday, August 7, 2014 10:12 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:38 PM
Points: 11,314, Visits: 13,101
Here are the options I know of for what you want to do and the SQL Server version(s) it will work on:

1. DDL Triggers - all versions
2. Stored Procedures that do all CRUD operations and do the logging as part of the procedure - all versions
3. Change Data Capture (CDC) - 2008 or later.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1600744
Posted Thursday, August 7, 2014 10:24 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
thank you
Post #1601016
Posted Thursday, August 7, 2014 10:27 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 14, 2014 4:48 AM
Points: 76, Visits: 346
Any tools available for this ?
Post #1601017
Posted Friday, August 8, 2014 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:38 PM
Points: 11,314, Visits: 13,101
sumith1andonly1 (8/7/2014)
Any tools available for this ?


Tools for which option?

I believe you can find some scripts in the scripts section of this site that will automatically generate audit triggers for all you tables.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1601146
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse