May 2, 2011 at 7:56 am
Hello,
I need help.
I want to extract multiple parts of sql statement string.
ex. I have the example of string:
UPDATE table SET XXX = '1', YYY = 2 WHERE ID = 24125;
So, I want to extract these values in bold. In general, names of columns changed via Update statement.
Thank you!
May 2, 2011 at 8:31 am
What's the big goal of the project here? Parsing all that is not going to be easy. Especially across an entire application.
May 2, 2011 at 8:39 am
I am working with SQL Audit Logs. I am auditing any update, delete, insert statement taken into specific table.
So, my goal is to extract column names that has been changed by users.
In this case, in the UPDATE statement, user may change multiple column values, therefore, I need to extract these column name, as in the example above.
I can select for example only a part of string, but not multiple, let say, the string between 'SET' and '=' .
SUBSTRING(statement,NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+0,
NULLIF(CHARINDEX('=',statement),0)-(NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+0))
In my case, I would like to extract all column names, if there are changed multiple column values.
May 2, 2011 at 8:42 am
asterman_3 (5/2/2011)
I am working with SQL Audit Logs. I am auditing any update, delete, insert statement taken into specific table.So, my goal is to extract column names that has been changed by users.
In this case, in the UPDATE statement, user may change multiple column values, therefore, I need to extract these column name, as in the example above.
I can select for example only a part of string, but not multiple, let say, the string between 'SET' and '=' .
SUBSTRING(statement,NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+0,
NULLIF(CHARINDEX('=',statement),0)-(NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+0))
In my case, I would like to extract all column names, if there are changed multiple column values.
I might be easier to implement CDC or DML audit triggers into change history tables.
Trying to parse that is going to a lot harder than those 2 solutions.
May 2, 2011 at 8:52 am
In my case, since I need also to keep track of DatabaseName, TableName, UserName - that's why I am using audit logs and I can not use CDC since it doesn't include these values.
Is there any idea how to extract multiple parts of string as in the case above?
May 2, 2011 at 9:00 am
I'm just trying to save you some time.
I don't know for sure but I'd bet a good some of cold hard cash that it took a team of 10 of the smartest programmers at microsoft 10 years to come up with the sql parser. That's 200 000+ hours of work. I'm pretty sure you don't want to take that route!
DB_NAME() and USER_NAME() are available in triggers. Tablename can be hardcoded at trigger creation are fetched back from the system tables are run time using @@PROCID (object_id of the trigger, then make your way back to the table name).
I've never used CDC but I'm pretty sure you have a way to know the DB and table name. For the user who made the change I don't know if it's there. Hopefully someone else will be able to answer that one
May 2, 2011 at 9:04 am
You seem to be right about CDC for the username.
So it looks like you'll have to use triggers to do this.
There are articles on this site to automatically create the tables and triggers for audit. Let me know if you can't find them.
May 2, 2011 at 9:10 am
Thank you for your suggestions. I will look at it to see whether I can switch to that way.
I have seen CDC and Change Tracking, but my goal is to deal with SQL auditing feature and its logs.
In this case, the logs every time keep the whole sql statement (insert, update, select or delete) in a column named 'statement'. Therefore, I need to manipulate to extract the required values as in the very first question.
May 2, 2011 at 9:15 am
asterman_3 (5/2/2011)
Thank you for your suggestions. I will look at it to see whether I can switch to that way.I have seen CDC and Change Tracking, but my goal is to deal with SQL auditing feature and its logs.
In this case, the logs every time keep the whole sql statement (insert, update, select or delete) in a column named 'statement'. Therefore, I need to manipulate to extract the required values as in the very first question.
You need a guru in parsing... which I'm not.
The real challenge is that you have 100s of possible template for valid update statements. The sheer amount of work required makes it almost impossible to help you here (unless someone has always done the work and is ready to share).
May 2, 2011 at 9:24 am
Your suggestions are right, since I am working more in static way, selecting from string for few cases of SQL statements.
Using CDC or Change Tracking is better since it offers general case as I need.
But, how to deal with my requirements, to extract DatabaseName, TableName, Username, ColumnName changed.
May 2, 2011 at 9:34 am
I was wrong about cdc, apparently you can't get the name of the person who did the change (unless maybe you manually put it in all the tables within the update statement).
If you don't already do that, then you'd need to create triggers on all tables.... which in that case I'd use the trigger solution anyways! :w00t:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply