October 8, 2008 at 9:19 am
Correct. And for the record, I do the same thing that Gail and Lynn do with the formatting. A lot of the SP's in our db declare variables like:
Declare @a varchar(50)
, @b-2 varchar(50)
, @C varchar(50)
, @D varchar(50)
which drives me nuts, despite my realization that it lets you easily comment one out and probably leads to less missing comma syntax errors overall.
So, usually, when I have to update one of them, I take the few extra minutes to change that to:
DECLARE @avarchar(50),
@b-2varchar(50),
@Cvarchar(50),
@Dvarchar(50)
October 8, 2008 at 9:31 am
The fastest way would be to buy a copy of SQL Refactor (from RedGate). Otherwise you're going to be spending hours doing replaces of the various keywords and fixing any places where the search-replace replaced incorrectly.
You should be able to get Enterprise manager to script all the procs as drop-create statements to a single file, which will make it easier to do.
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
October 8, 2008 at 9:34 am
Broken record time. I wouldn't waste my time reformatting stored procedures just for consistency purposes. I would spend my time on more productive efforts that will have a positive impact on the system/application. If the procedures are working and don't need modification, don't touch them. When you need to modify or update a procedure, that is the time make appropriate changes in formatting as well as function.
If you determine that a procedure is inefficient, that too is when you should look at changes in formatting.
Just modifying procedures because they look inconsistent isn't a reason to make changes.
😎
October 8, 2008 at 5:29 pm
karthikeyan (10/8/2008)
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.
You guys have plenty of time to waste on nothing.
BTW, karthikeyan, you and your manager get together in front of your monitor, open BOL and try to find single example of SQL code in there where keywords are coded in lower case.
Obviously your manager is new not only to this project but to SQL as well.
People with no clue very quick on enforcing standards on something they have no idea about.
_____________
Code for TallyGenerator
October 8, 2008 at 9:00 pm
Sergiy (10/8/2008)
karthikeyan (10/8/2008)
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.
You guys have plenty of time to waste on nothing.
BTW, karthikeyan, you and your manager get together in front of your monitor, open BOL and try to find single example of SQL code in there where keywords are coded in lower case.
Obviously your manager is new not only to this project but to SQL as well.
People with no clue very quick on enforcing standards on something they have no idea about.
Now now now...Just because they want to enforce C# standards on TransactSQL (which is not case-sensitive, so has no care whether you SELECT, SeLeCt or select) doesn't make them clueless. Obtuse, perhaps; refusing to acknowledge a losing battle when you see one, perhaps; not understanding that T-SQL should NOT read like procedural code, nor should it be built that way if you hope to have any perf left, definitely....
I suppose that might even be the answer: why not write a C# program to scan through and scrub all of your T-SQL code? Since it doesn't sound like we much care what the code does, but rather how it looks, might be worth the time.....:P
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2008 at 7:42 am
"You should not, but if you must" ...
From Enterprise Manager
Select your database. Click on right-hand mouse button. Click on Generate SQL script.
In the first tab, "General" click on the "Show All" button and then check ON both the "All Stored Procedures" and "All User Functions" boxes.
In the second tab, "Formatting", check ON both "Generate the Create" and "Generate the Drop" .
Then you can save ALL your stored procs / user defined functions code in a common file. Open said file with a text editor and manually change the keywords casing.
-- "select" for "SELECT",
-- "where" for "WHERE"
There are not so many keywords in SQL Server, so this is not an inordinate amount of work to do. Do ** NOT ** use Notepad, this is excruciatingly s --- l --- o --- w. Heck I do it in the results pane of the Query Analyzer.
Of course, you hope that table names or column names do not contain the keywords, such Table myEndResults or column ExtendedTotal, both might end up as myENDResults and ExtENDedTotal, which defeats your purpose. You could try to look for keywords plus a leading and trailing blank character - maybe if your coders are using tabs...
After that, run the modified file in the Query Analyzer to DROP and RECREATE the stored procs and udf's. Of course, backup your database BEFORE you do this.
Then, what you are likely to get into other issues:
- some coders used TABS while others used spaces,
- some coders wrote WHILE BEGIN, others wrote,
- over time, some columns have become irrelevant and are creating confusion,
- coders used different casing and hyphens in creating table names and columns...
....WHILE
........BEGIN. (in fact, indenting style is almost like a religion for some).
You get into this deeper and deeper.
The you find out that the ESSENTIAL part COMMENTS are not even worth the bytes they use like SET @li_Count = @li_Count + 1 -- increment counter while hard-to-get-it pieces of code are not commented, or comments are just plain misleading.
And then you realize that there are almost duplicates of stored procs or udfs and worse, some that are extremely inefficient (were OK for a few dozens of records and which are performing poorly after unexpected growth in the number of records) or are extremely difficult to modify to accommodate a slight change in specs ...
Then you find abusive usage of cursors because they were easy to implement by applying non set-based programming (loops) and are later causing performance problems.
Then you find that indexes are missing.
Then you find that at some point referential integrity was not applied, leading eventually to inconsistent data. After all, this is a RELATIONAL database.
Yes, I too like upper cased keywords because:
1. this is how SQL code was generally shown in examples when I started out,
2. this makes the embedded SQL Statements (duck, flame war coming...) within, say
....a C# application, standout as opposed to C# keywords.
When you are dealing with humans, absolute consistency is a dream, regardless of how pleasing it is (especially when it meets YOUR casing standard). You have to accept inconsistencies such as casing, hyphenation, etc.
While case correction it is easily doable, I agree with others that casing is a minor concern as opposed to some of the other issues I mentioned above. Casing should be the least of your worries.
Regards
October 10, 2008 at 8:29 am
Yes, I too like upper cased keywords because:
Except he's not trying to make them uppercase, he's trying to make keywords(and only keywords) all lower. Hence the amount of outrage on this post 😉
Your recapping of all the issues he's likely to run into reminds me of another. If he's got outdated SP's that aren't really run anymore(and reference missing columns in tables / other things that no longer / partially exist), won't those cause failures and lead to a lot of manual deletion from his drop/recreate script?
Here is an even more insidious problem. If you create an SP, and then go into EM and manually modify the name (perhaps you added a 2, 3, 4 while you were developing it, and then went in and changed the name to remove the 4), you will end up with a script like this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TESTSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[TESTSP]
GO
...
CREATE PROCEDURE dbo.TESTSP4
While you may never have done this, can you guarantee that no developer or DBA that has EVER worked at that company has done it?
Basically we keep coming full circle to the same thing. Figure out how to convince your manager it is a bad, bad, bad idea and that the list of problems it can cause hasn't even been fully discovered yet.
October 10, 2008 at 10:20 am
Except he's not trying to make them uppercase, he's trying to make keywords(and only keywords) all lower
OOPS ! Missed the "lower case" part. That ARBITRARY one indeed runs contrary to every text book example I remember having seen. I would most certainly raise a mighty roar if I saw someone "improving" my code like this. If overruled, my answer to any complaint would certainly "fix your own changes".
As for the rest of my previous post, it did apply to keywords only (with no guarantee as to what happens if a table name of column name contains the keyword). Although I offered a way to do it without installing a third-party app (regardless of their great value), I was using this thread to voice my own concerns over the original question.
And I appreciate your additions to my short list of more important things to do than prettify keyword casing.
It does seem that someone's efforts are [gently said] misguided.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply