Changing sql keywords to lower or upper

Question

Changing sql keywords to lower or upper

karthik M

One Orange Chip

Points: 29139
More actions
October 8, 2008 at 3:33 am

#71929

All,
I have to convert all the keywords in a procedure from upper case to lower case.
Say for example,
Create procedure p1
(
@eno INT
)
AS
begin
SELECT eno,COUNT(eno)
FROM emp
Where eno > 50
GROUP BY eno
Having count(*) > 1
if @@ERROR > 0 and @@ROWCOUNT = 0
Begin
raisError 99999 'Error occured...'
END
--end of procedure
END
Not even a single character should in UPPER case.
Say for example,
Where
Having
raiseError
Begin
My requirement is if any upper case letter found in the keywords i have to change it to lower case.
Inputs are welcome !
karthik

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

very easy ...CTRL+H then you have the options Find What = select ...Replace with: SELECT! then Find Next and you will find all them and if you want replace all of them ...

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Dugi,

Good idea 🙂

I have to change more than 400 stored procedure. So i am looking some common code to do it.

karthik

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Why would you do something like this?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dugi SSCoach Points: 17998 More actions · Answer 4

Garadin (10/8/2008)
Why would you do something like this?

Ok good question couz you no need to change them for execution...are you doing such thing just to see the code more pedantic or what is the reason - little bit curious!?

Another alternative is if you have VS 2008 Team Suite ...inside you have the incorporated the database edition tool that can help you to change for example to change the column name everywhere in your objects. You can try if it works also for the SQL statements! I'm not sure 100 %!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 5

Anybody want to share their idea apart from VSS method ?

karthik

steveb. SSC-Forever Points: 46733 More actions · Answer 6

You can geta copy of SQL refactor from Red-Gate this will upcase all your keywords for you , it works really well.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Dugi (10/8/2008)
very easy ...CTRL+H then you have the options Find What = select ...Replace with: SELECT! then Find Next and you will find all them and if you want replace all of them ...

Even easier way. Script one or more procs into QA (I'm sure Enterprise manager has an option to script drop and create statements for all procs in a DB)

Ctrl - A (highlight all)

Ctrl - Shift - L (lowercase highlighted text)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Gila,

I dont want to change all the text into lower case.

Just i want to change upper case keywords into lower case.

say for example,

1)

SELECT eno

from emp

expected result:

select eno

from emp

2)

SELECT eno

FROM emp

expected result:

select eno

from emp

karthik

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

I'm waiting to see why you need to change the case of everything in your stored procs from UPPER CASE to lower case.

😎

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Lynn,

Well 🙂

A team around 10 members developed my project three years back. They had written more than 400 stored procedures. They mixed upper case and lower case in each and every procedure. It is not looking in the standard way.

They wrote something like if EXISTS (select * FROM emp).... Just saying...

I am providing support for this project. Sometime user may want to change the report format or adding new functionality to the procedure. It depends on the user.

Actually we (mine and my manager) new to this project. My manager has looked some procedure few days back. He is not happy with the procedure case format. He asked me to maintain the same format i.e lower case in all of the procedure.

karthik

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

I have to deal with the same thing where I work. If I need to modify a procedure, that is when I make any changes to formatting (capitalization, indention, etc), otherwise I just leave it alone. It's just extra work that doesn't bring any real value to just modify a procedure because it doesn't look consistent.

😎

Garadin One Orange Chip Points: 29613 More actions · Answer 12

If your goal is to make your procedures more easily readable and maintain proper formatting... why wouldn't you upcase all the key words rather than lower case them? This seems like the kind of thing that will cause somebody to post a topic in 6 months named "Changing sql keywords to upper from lower".

An alternative solution to changing the way everything is coded in your database is possibly using SQLPrompt. I believe I saw that it had (we don't use it here) a formatting option to format SQL to your own specifications, and then your boss could have it his way, you could have it yours, and you wouldn't have to mandate that it was all done in a specific way. I know that I for one would be extremely distracted by all lower case SQL. I can barely stand ad hoc crap that I know I'll be deleting in 30 seconds being lowercase.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Lynn Pettis (10/8/2008)
I have to deal with the same thing where I work. If I need to modify a procedure, that is when I make any changes to formatting (capitalization, indention, etc), otherwise I just leave it alone. It's just extra work that doesn't bring any real value to just modify a procedure because it doesn't look consistent.
😎

Indeed. I used to do the same. Time spend changing the formatting of 400 odd procs is time better spent doing productive work.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 14

Garadin,

If your goal is to make your procedures more easily readable and maintain proper formatting... why wouldn't you upcase all the key words rather than lower case them?

Whatever format is, i need to convert it.

say for example,

select * FROM emp

Where eno = 1

Again i have check, which keywords are not in upper case.

An alternative solution to changing the way everything is coded in your database is possibly using SQLPrompt

This is red-gate's software. Right ?

karthik