| Author |
 Topic  |
|
fafastrungen
Starting Member
5 Posts |
 Posted - 07/20/2007 : 06:45:54
|
Hi, I'd like to build a sql parse to understand stored procedures.
I have too many stored procedures to check one by one, so I need a tool that makes this but all tools I've found can not achieve these tasks in a safe way, so I' thought to build my own sql parser to understand the sp's bodies.
What I need is to know where are stored all the SQL instructions, starting with the basic instructions like SELECT, INSERT, and so on and finishing with the Sql Server own instructions like GETDATE, SET NOCOUNT and so on.
I need the complete list of the sql instructions to understand a full sql.
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 07/20/2007 : 07:06:00
|
You should read Books On Line, from cover to cover. Then to see the text in a stored procedure use sys.sql_modules
Jim |
 |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/20/2007 : 07:25:52
|
build your own sql parser??? good luck.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
mattyblah
Starting Member
USA
49 Posts |
Posted - 07/20/2007 : 11:28:25
|
| The ability to write a sql parser would far surpass the ability to understand stored procedures. but, if you want, take a look at some compiler design books. it should help you understand how the parsing process works with programming languages. |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 07/20/2007 : 13:43:13
|
building a parser for any language is non-trivial. have you ever done it before?
elsasoft.org |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 07/20/2007 : 17:04:28
|
quote:
Originally posted by fafastrungen
Hi, I'd like to build a sql parse to understand stored procedures.
I have too many stored procedures to check one by one, so I need a tool that makes this but all tools I've found can not achieve these tasks in a safe way, so I' thought to build my own sql parser to understand the sp's bodies.
What I need is to know where are stored all the SQL instructions, starting with the basic instructions like SELECT, INSERT, and so on and finishing with the Sql Server own instructions like GETDATE, SET NOCOUNT and so on.
I need the complete list of the sql instructions to understand a full sql.
Are you insane? |
|
|
|
Haywood
Posting Yak Master
USA
221 Posts |
Posted - 07/20/2007 : 17:07:05
|
quote:
Originally posted by rudesyle
Are you insane?
Only ask, if you really want to know...  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 07/20/2007 : 17:44:25
|
Good luck with your little sql parser project.
Be sure to post the code when you're done.
CODO ERGO SUM |
Edited by - Michael Valentine Jones on 07/20/2007 17:44:51 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 07/20/2007 : 17:54:50
|
And you want this to be done in SQL Server?
Peter Larsson
Helsingborg, Sweden |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 07/20/2007 : 18:42:14
|
Lol (to comments and question).
The best SQL Parser is likely to be you. As mentioned, read Books Online. Reading cover to cover is likely to be excessive for your needs. Just run through some SQL tutorials (such as http://www.w3schools.com/sql/default.asp) if you don't know the basics and then work your way through the procedures, looking up each function & command that is alien to you in Books Online as you come across them. There is no short cut to knowledge - this is a good thing otherwise the job market would be pretty lousy. |
|
|
|
fafastrungen
Starting Member
5 Posts |
Posted - 07/23/2007 : 03:27:59
|
In fact I need to check the stored procedures and how to optimize them, that's why I need to understand the SQL code becouse SP's doesn't respect a fixed way in their bodies.
This could be a basic SP:
Alter Procedure SP1 As
Set NoCount ON
Select * from Sys.Tables
Go
but in the text's body this is the result:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[SP1] As
Set NoCount ON
Select * from Sys.Tables
so if I need to optimize it, I need to understand the code inside it.
And yes, I'm insane, that's why I've choosen a job with computers. |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/23/2007 : 06:23:31
|
i don't get it... it's the same body.
what's your problem?
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 07/23/2007 : 07:29:06
|
If you want to optimize queries, put the body of a sproc in the query analyzer and hit ctrl + L, This will give you SQL's execution plan for the query. Look up all the different seeks, scans, etc., and this will give you a starting point to see what SQL is doing so that you can then optimize.
Jim |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 07/23/2007 : 07:31:40
|
I'd agree with everyone here that writing a parser isn't the way to accomplish your goal. If you really do want to write a parser though I'd start here: http://www.devincook.com/goldparser/
===============================================
Creating tomorrow's legacy systems today.
One crisis at a time. |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 07/23/2007 : 10:33:55
|
i'd start with building a new OS first...
-ec |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
fafastrungen
Starting Member
5 Posts |
Posted - 07/24/2007 : 04:02:50
|
I have many SPs on a server so I can't go one by one manually, I need something that do the hard work.
These SPs are created with CodeSmith, but CodeSmith doesn't generate optimized SPs, thats why I need to check the SPs.
|
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/24/2007 : 04:31:18
|
oh so you want to optimize your sprocs?
you'll have to go through one by one
no parser will help you there.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 07/24/2007 : 06:44:53
|
quote:
Originally posted by spirit1
you'll have to go through one by one
no parser will help you there.
Sounds a bit like hard work that spirit. You would have thought SQL Server could optimise them itself if it was any good. Use Oracle! |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/24/2007 : 07:32:51
|
silly me... i don't know what i was thinking
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 07/24/2007 : 10:39:45
|
procs are easy to optimize. just comment out the body. then they will run really fast.
elsasoft.org |
|
|
|
Topic |
|
I want to build a sql parser
