SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 I want to build a sql parser
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

fafastrungen
Starting Member

5 Posts

Posted - 07/20/2007 :  06:45:54  Show Profile  Reply with Quote
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
2869 Posts

Posted - 07/20/2007 :  07:06:00  Show Profile  Reply with Quote
You should read Books On Line, from cover to cover. Then to see the text in a stored procedure use sys.sql_modules

Jim
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/20/2007 :  07:25:52  Show Profile  Visit spirit1's Homepage  Reply with Quote
build your own sql parser??? good luck.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

mattyblah
Starting Member

USA
49 Posts

Posted - 07/20/2007 :  11:28:25  Show Profile  Reply with Quote
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.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 07/20/2007 :  13:43:13  Show Profile  Visit jezemine's Homepage  Reply with Quote
building a parser for any language is non-trivial. have you ever done it before?


elsasoft.org
Go to Top of Page

rudesyle
Posting Yak Master

110 Posts

Posted - 07/20/2007 :  17:04:28  Show Profile  Reply with Quote
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?
Go to Top of Page

Haywood
Posting Yak Master

USA
221 Posts

Posted - 07/20/2007 :  17:07:05  Show Profile  Reply with Quote
quote:
Originally posted by rudesyle

Are you insane?



Only ask, if you really want to know...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/20/2007 :  17:44:25  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/20/2007 :  17:54:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And you want this to be done in SQL Server?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 07/20/2007 :  18:42:14  Show Profile  Reply with Quote
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.
Go to Top of Page

fafastrungen
Starting Member

5 Posts

Posted - 07/23/2007 :  03:27:59  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/23/2007 :  06:23:31  Show Profile  Visit spirit1's Homepage  Reply with Quote
i don't get it... it's the same body.
what's your problem?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 07/23/2007 :  07:29:06  Show Profile  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 07/23/2007 :  07:31:40  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/23/2007 :  10:33:55  Show Profile  Reply with Quote
i'd start with building a new OS first...




-ec
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/23/2007 :  15:35:22  Show Profile  Reply with Quote
Sounds like you just want the ability to interrogate all of the sprocs based on a key word

http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

fafastrungen
Starting Member

5 Posts

Posted - 07/24/2007 :  04:02:50  Show Profile  Reply with Quote
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/24/2007 :  04:31:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
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
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 07/24/2007 :  06:44:53  Show Profile  Reply with Quote
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!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/24/2007 :  07:32:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
silly me... i don't know what i was thinking

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 07/24/2007 :  10:39:45  Show Profile  Visit jezemine's Homepage  Reply with Quote
procs are easy to optimize. just comment out the body. then they will run really fast.


elsasoft.org
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000