On Wed, Jun 18, 2008 at 10:13 AM, Malcolm Greene <[EMAIL PROTECTED]> wrote:
> > I have some case statements that are 10 lines long per statement > > Inspired by Stephen Russell's huge blocks of SQL (and as a cheap shot > way to re-ask my SQL analysis utility post from yesterday from a > different angle), how do you manage your huge blocks of SQL? > > By manage I mean format, pretty print, version, audit, comment, re-use, > etc? > > And when do your blocks of SQL become so large that you need to > re-factor them by creating re-usable views/tables and/or using a > different tool (Informatica, etc). > ----------------------------------------------------- I use to write SPs that did the second and third steps in the job of taking a pull of data from any source and Denormalize it and then normalize it to their view patterns. these processes would take 1-2-3 thousand of lines of code depending on the source. I had a FPD system with 160 tables So there was one SP for all of that. Anyway back to the Q. I had a .sql file for all of the code bases. We had a source control system that accepted it. I required a description of every file in the header as to what it did, as well as an update line for any recheckin. We had a single head person for this so it was someones job. We also made our own source table system that you could query on. all header descripts were there. All tables were referenced. All Params were referenced. With this add on we were able to find just about anything in no time at all. Other tables were for definition of what the data was and where it came from. Studies etc. Content of that system? 20,000 rows as a bare minimum. When I left it was over 150,000 Now that are you trying to do in your organization? find stuff? Are you dealing with views, SPs, triggers, ??? Just to search for text in your code in the database try this: declare @query varchar(100) set @query = ''WhatEverYourLookingFor' SELECT DISTINCT name, type FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id WHERE text LIKE '%' + @query + '%' ORDER BY name This will find that text and let you the objects name know what type of an object it is. HTH -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
