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

SQL Parse?? Expand / Collapse
Author
Message
Posted Friday, October 19, 2012 9:25 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:35 PM
Points: 727, Visits: 1,392
Hey everyone,

Trying to find a bit of help with a project I am working on.

Basically, we are trying to take a normal ad-hoc query statement:

select col1, col2, col3
from table1 as t1
inner join table2 as t2 on t1.cola = t2.colb
where t1.colc = 'abc'
and t2.colc = 12345

and replace it with something that we could actual review:

select col1, col2, col3
from table1 as t1
inner join table2 as t2 on t1.cola = t2.colb
where t1.colc = @var and t2.colc = @var

So we are pulling a trace an the first statement is what would get shown in the TextData of the trace. We want to be able to store off the trace into a table, but store if off like the 2nd statement so we can group all of them together.

Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.

Thanks,

Fraggle
Post #1374889
Posted Sunday, October 21, 2012 8:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
http://www.scalesql.com/cleartrace/

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1375241
Posted Monday, October 22, 2012 12:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
Actually parsing SQL is quite a simple thing, once you find the right .Net class

Here is one example of what you can do ..

http://tsqlsmells.codeplex.com/

Should be a relatively simple thing to parse the SQL and replace the values with @Var1 @Var2 etc.




Clear Sky SQL
My Blog
Kent user group
Post #1375260
Posted Monday, October 22, 2012 7:34 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:35 PM
Points: 727, Visits: 1,392
Yea, I have looked at this. However, it aggregates everything and within the system I am trying to do, add's a lot of unnecessary overhead. I am already putting the data into tables in an aggregation. I just need the parsing part.

Unless you are telling me there is a way to just do the parsing, which I haven't seen.

Fraggle
Post #1375423
Posted Monday, October 22, 2012 7:38 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:35 PM
Points: 727, Visits: 1,392
Sadly I am only just now learning OO and C#. And all of my developers are pretty busy for the next few months on a major project. So much so, that I have been told by the development manager that they don't have time for this until March, unless by chance we get the project done early (yeah, when was the last time that happened!).

Any other thoughts?

Thanks,

Fraggle
Post #1375427
Posted Monday, October 22, 2012 8:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 12,744, Visits: 31,067
i've played with Microsoft.SqlServer.Management.Smo.Scripter that is part of the microsoft.sqlserver.managment.sdk.sfc ;

using that, you can tokenize any SQL statement.
i did it when i was playing with making my own SQL reformatter/prettifier, so if you had a suite of strings you wanted to abstract/tokenize out, that's the way to do it;
you can do it in a programming language, and there's a brief example on an SSC blog here:
http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/


here's the master list of tokens that can appear in a given SQL statement:
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.tokens(v=sql.105).aspx


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1375455
Posted Tuesday, October 23, 2012 7:11 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 1,470, Visits: 4,272
Fraggle-805517 (10/19/2012)

...
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
...

SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.
http://msdn.microsoft.com/en-us/library/ms189741.aspx

For example:

SELECT TOP 5 query_stats.query_hash, 
SUM(execution_count) Sum_Execution_Count,
SUM(query_stats.total_worker_time)
/ SUM(query_stats.execution_count) AS Avg_CPU_Time,
max(total_logical_reads) Max_Reads,
MIN(query_stats.statement_text) AS Statement_Text
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY Avg_CPU_Time desc;




"Winter Is Coming" - April 6, 2014
Post #1376020
Posted Wednesday, October 24, 2012 7:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:35 PM
Points: 727, Visits: 1,392
Eric M Russell (10/23/2012)
Fraggle-805517 (10/19/2012)

...
Now, I don't know if this is really called parsing or replacing or something completely, different, but I have a hard time believing that someone hasn't done this before. I realize that there are a lot of complect things that migth not be able to be handled, but if anyone has some links, thought, options for me, it would be a huge help.
...

SQL Server's query plan cache assigns an 8 byte hash value that identifies queries with similar SQL text but disregarding parameter values. It sounds like perhaps the goal of your project is to aggregate performance metrics like CPU or disk reads by common query type. If so, then you can achieve this by simply querying sys.dm_exec_query_stats instead of using SQL Profiler traces.
http://msdn.microsoft.com/en-us/library/ms189741.aspx

For example:

SELECT TOP 5 query_stats.query_hash, 
SUM(execution_count) Sum_Execution_Count,
SUM(query_stats.total_worker_time)
/ SUM(query_stats.execution_count) AS Avg_CPU_Time,
max(total_logical_reads) Max_Reads,
MIN(query_stats.statement_text) AS Statement_Text
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY Avg_CPU_Time desc;



This does get me some stuff, but not everything we will be doing with it. Beside the fact that I can't pull Avg, Stdev, and Median, I am also using this process and an auditing system to see what is being called from specific users when the login to production.

Fraggle
Post #1376486
Posted Wednesday, October 24, 2012 7:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:35 PM
Points: 727, Visits: 1,392
Lowell (10/22/2012)
i've played with Microsoft.SqlServer.Management.Smo.Scripter that is part of the microsoft.sqlserver.managment.sdk.sfc ;

using that, you can tokenize any SQL statement.
i did it when i was playing with making my own SQL reformatter/prettifier, so if you had a suite of strings you wanted to abstract/tokenize out, that's the way to do it;
you can do it in a programming language, and there's a brief example on an SSC blog here:
http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/


here's the master list of tokens that can appear in a given SQL statement:
http://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.sqlparser.parser.tokens(v=sql.105).aspx


Thanks, I will take a look at this.

Fraggle
Post #1376487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse