Finding Updated Columns from within the Stored Procedures

Question

I am able to find out the columns updated within the trigger of the table. However the trigger is kind of big, I want to reduce its size as much as possible. So now, I want to create a generic stored procedure and find out the updated columns from within the stored procedure.

Here is the SQL query that finds out the updated columns

SELECT  @idTable = T.id 
FROM    sysobjects P JOIN sysobjects T ON P.parent_obj = T.id 
WHERE   P.id = @@PROCID

---- Get COLUMNS_UPDATED if update
DECLARE @Columns_Updated VARCHAR(50)

SELECT @Columns_Updated = ISNULL(@Columns_Updated + ', ', '') + name 
FROM syscolumns 
WHERE id = @idTable
  AND CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT, 2), colorder - 1)  > 0

Could some one help me out as to what am I suppose to do to achieve my goal

@marc_s i dont know if its the right ans to your question. But i am using sql server. Please bear with me if i am wrong — ik024, Sep 17 '13 at 12:54
Check http://stackoverflow.com/questions/2136861/t-sql-what-columns-have-changed-after-an-update and http://stackoverflow.com/questions/8049810/how-to-get-updated-column-names-in-sql-server — Yuriy Galanter, Sep 17 '13 at 15:19
I AM ACTUALLY LOOKING FOR A GENERIC STORE PROCEDURE TO AUDIT THE TABLE — ik024, Sep 18 '13 at 05:22

score 0 · Accepted Answer · answered Nov 05 '13 at 12:07

If you want to create an sp that will execute whenever you want and see what was updated database-wide since the last run of this sp, then I don't think it can be done. I would advise to either use the built-in sql server 2008 audit functionality or use triggers as Yuriy Galanter already pointed out.

Finding Updated Columns from within the Stored Procedures

1 Answers1