Controversial: SQL
-
I find it handy to store them as pass-through queries in an MS Access database. You can give them names that work for you, and then sort them, filter them, search for them, etc. I also wrote a VBA function to let me search for any text string in all my Access queries in case I want to find which ones contain a certain table or field name and so on.
-
A side benefit - I think you will really like Agent Ransack - as a bonus. I have used this tool for years. Its the one tool I cannot live without. You know you got the queries.. you just need help finding them. Comment them over time. Comment new ones when you make them. Then let Agent ransack do the work. Keep It Simple, keep it moving.
-
For very special, or big, or being used a lot, queries (or for clients who can't spell SQL :-D ), I use something that you will probably hate. I use Java :) I make a program in Java (basically a custom SQL generator) where I insert the code inside a method that receives parameters that allow to configure the query in things like database/table/field names, which fields to return, insert sub-queries, etc. Then document with Javadoc as with any other program. All the queries are inside the same Java program, are selectable and configurable via command line or a web page that dynamically requests more configuration based on what you requested and configured so far. Using the generated Javadoc or an IDE on the program source makes it easy to find anything. The program makes easy to get the configured SQL. This has the advantage that you can tweak the Java source to add more functionality to the queries and make that selectable via parameters. You can even pass the program to clients and they will happily build some queries they need by just answering configuration questions and never touching SQL. I have done this for a client in the past in which the program would generate SQL and JavaScript (to interact with the SQL via web page) for their web site recommendation system using Similarity Matrices, Friends-of-Friends and a few other algorithms implemented in SQL. Since their recommendation system used the same algorithms to recommend different things (people, items, lists of people-item pairs, etc), this method was easier for me to maintain and they were very happy to be able to generate the required SQL in less than 5 minutes by answering a few questions instead of manually modifying each time the almost 500 lines of SQL code for the Similarity Matrix alone. They were even able to use the program by themselves, without requesting my help, to generate SQL to recommend things that were never mentioned to me. Unfortunately, I can not show any of those programs as they are protected by IP :( Yes, this is convoluted, but makes things easier on the long run.
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
I am always late to the party. HAHAHA. But then I get to read everyone elses comments before I make my own. What I use is a blend of notepad++ or SSMS and multiple fairly large what I call scratch files. For differing companies and projects I create a scratch SQL project which is mostly commented out sql that includes databases, SPs, Views etc... and then one or 15 differing select statements/kewl sql statements that work for this database scheme. Then I use Agent Ransack when I am searching for something esoteric that I need for a new client/project. It works for me and I usually end up cobbling something together from inside the scratch file then copying and pasting into a new SQL for the new SP or View and away I go. What works for you I guess. PS I do love Agent Ransack.
To err is human to really mess up you need a computer
-
OriginalGriff wrote:
I have sections in my text file ... because I'm lazy ...
:thumbsup: That made me laugh too. I would like: 1) way to pick tables and fields (returned in query) 2) have notes that remind me what the query does 3) have a good way to find them again -- this wouldn't be easy to do I end up saving large SQL text files all over the place and then later searching through them and it takes just about as long to find them as it does to just think it all out again. I'm just wishing over here. :-D It would be very difficult to really create a good organizer. I guess I'm even lazier than I thought. (which I didn't think was possible)
I use grepWin on windows to search the content of files. Has regex capabilities too. I use it all the time to find old SQL queries in our installer's collection of SQL files. The secret is to put all the text files in the same (root) folder. Bond Keep all things as simple as possible, but no simpler. -said someone, somewhere
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
My company uses SourceGear Vault Standard to store tables, stored procs, functions and queries, but I keep a folder of SQL named with a date and purpose. An example would be '20201201_RestoreDataForCustomer'. The date gives me a context to find them more quickly.
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
-
My company uses SourceGear Vault Standard to store tables, stored procs, functions and queries, but I keep a folder of SQL named with a date and purpose. An example would be '20201201_RestoreDataForCustomer'. The date gives me a context to find them more quickly.
Rusty Bullet wrote:
An example would be '20201201_RestoreDataForCustomer'. The date gives me a context to find them more quickly.
:thumbsup: That's one of the tricks I've employed too. but, it also seems to point to a problem that is begging for a management solution. Thanks
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
-
I am always late to the party. HAHAHA. But then I get to read everyone elses comments before I make my own. What I use is a blend of notepad++ or SSMS and multiple fairly large what I call scratch files. For differing companies and projects I create a scratch SQL project which is mostly commented out sql that includes databases, SPs, Views etc... and then one or 15 differing select statements/kewl sql statements that work for this database scheme. Then I use Agent Ransack when I am searching for something esoteric that I need for a new client/project. It works for me and I usually end up cobbling something together from inside the scratch file then copying and pasting into a new SQL for the new SP or View and away I go. What works for you I guess. PS I do love Agent Ransack.
To err is human to really mess up you need a computer
rnbergren wrote:
It works for me and I usually end up cobbling something together from inside the scratch file then copying and pasting into a new SQL for the new SP or View and away I go.
Yep, that's what it's like. That's why this is interesting to me because it seems like there would be one good solution, but it takes a lot of work to bring together a good way to manage it all. Thanks for your input.
rnbergren wrote:
PS I do love Agent Ransack.
another poster mentioned this and i'll be checking it out.
-
I use grepWin on windows to search the content of files. Has regex capabilities too. I use it all the time to find old SQL queries in our installer's collection of SQL files. The secret is to put all the text files in the same (root) folder. Bond Keep all things as simple as possible, but no simpler. -said someone, somewhere
Yeah this is the way most SQL solutions that people have mentioned are done. Text files in a directory, maybe use a file naming convention and then a search tool to find stuff in the SQL itself. It all works and you can wrap a process around it. It just seems like there would be a nice tool.
-
Save them as .sql files and give them good file names. Then organize them into folders. In other words, treat them like any other application source code.
That's really the main way to do it. I just don't like sql too much, often forget it and find that since I only have to design queries every few years I am very bored by it. :-D So, if I could find a way to manage them so I could just find the one I want very easily I would be happy (and it would promote my laziness). :laugh:
-
I don't hate Java, actually. I really like Java as a language. Also, this sounds like the kind of thing that I was thinking might be going on out there -- custom solutions that help you design sql queries and manage them(for devs). :thumbsup:
I meant that you would hate the methodology of writing SQL using another language, not the language itself, since it is more work initially. You have to break the SQL code into pieces to insert variables in between, write the program code and document it. Most of the times I mention this methodology people look at me as if I just told them to program everything in assembly! I use Java for this since it is very easy to convert to a servlet and have the interface on a web page, although I prefer to use the command line. My knowledge of web technologies is limited and all my pages end up looking like :doh:
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
What, can't you just ask the computer "give me this data" and it writes the SQL query for you? "Hello, computer." "Use the keyboard? How archaic!" I hope y'all know what movie I'm quoting. ;)
Latest Articles:
Thread Safe Quantized Temporal Frame Ring Buffer -
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
evernote or onenote since they allow for naming sub tabs, I can categorize them, make it a little faster to search
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
I use a private GitHub repo, and use Agent Ransack on my local laptop to find specific files by key words or phrases.
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
I wrote a little program called Code Vault it lets me name the snippet of code with a date saved and the code It is searchable from experience it needs a one character field where you enter C for create or U for Update need to rewrite it as it is in VB 6 Just a suggestion
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
Why use text files? A database can store strings. So if you want searchable storage of queries why not make a table?