Controversial: SQL
-
There's no great way to : 1) store and categorize good sql 2) also would like a way to insert table names and field names so you could select some things and then just fire off the SQL. 3) need a place for notes about what the sql actually does. I tend to forget sql very fast -- sql is boring and declarative and I always have to look at it too much to remember what it does. Sometimes I just want to find an old query that does that thing you know? :)
Sounds like templates: Template Explorer - SQL Server Management Studio (SSMS) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Well, I have something like that too, but it's difficult to find the right one amidst all of the other chaff. I wish there was a way to categorize them or something like that. And don't offer me the idea of making sections in my text file, cuz i'm lazy (of course -- I'm a dev). :-D
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
SQL is too broad term. IBExpert[^] for Firebird has it and much more[^] .
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
A lot of tools come to mind on this topic. Notepad, or Notepad++: I recommend using "AGENT RANSACK" a very fast multi-threaded Search engine. (You can start the search in your ROOT file for the SQL files) (Side note: This is my number one favorite tool) Organize the SQL files, by INDEXING Them (aka: "Tags") INDEX: (Example) #DATE Ex: 01/01/2021 #PROJECT Ex: (NAME) #TYPE Ex: (SELECT INSERT UPDATE DELETE) #NOTATION Ex: That query for the thing I had to do at 1AM ...more as needed ---------------------------------------------------------------- Open "Agent ransack" and search on terms in the sql. This could be all put in a database, but I think that may be a bit too much. You want something simple, and you want to find it fast. You already have a body of SQL that is not indexed. Start indexing all NEW SQL FILES, and as you revisit the old ones, (that are not indexed), Index them as you go along. Keep it all in one file, or multiple files, doesnt matter much with Agent Ransack. So what you wind up with is your very own Google Search for your sql. Keep It Simple, keep it moving.
-
I keep a text file of 'em: "GoodSQLQueries.txt" it's called. It's easy to copy'n'paste 'em when I need 'em. Or just use an SP. What's the problem?
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I use OneNote -- I have a notebook for each database, with sections labeled by tables and/or area. Each query or code segment has a comment briefing describing what it does. This makes hunting for things much easier.
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
I would suggest a couple of things: - a stored procedure that is just a collection of these wonderful SQL statements that is commented well and set up to NOT be runnable (comment out the whole thing between /* and */, probably) - set up a comment area above each piece of code with keywords or details about what the code does and what it would be useful for - using a search (CTRL-F) on the stored procedure you called GreatCodeToMaybeReuseSomeday (or whatever) to find the SQL of interest. - Copy and paste the code you found. Probably the closest to "think it and it will appear"
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
Just a thought: Use a library like JsonQL to convert SQL to a JSON representation and store them in MongoDB with descriptive metadata. Search on the content and/or metadata.
-
Quote:
There is no good way to store good SQL queries for future use.
What's wrong with stored procedures?
Member 13301679 wrote:
What's wrong with stored procedures?
I mean like a development studio type of thing where you can select a particular SQL statement that you want to run manually when you're examining data and designing queries. We use SPs for all interactions with DB but I'm talking more about a code reuse type of thing. I guess I could create a library of SPs and then run those when I need to, but I'd still need a way to manage them with names and stuff so I could remember what they do. I guess it is really because I'm in the midst of designing queries for a large system and I only have to do this every 2 or 3 years so I have a long time away from it.
-
How about [Solution Explorer - SQL Server Management Studio (SSMS) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/ssms/solution/solution-explorer?view=sql-server-ver15) ?
Check out my blog at http://msdev.pro/
-
Member 13301679 wrote:
What's wrong with stored procedures?
I mean like a development studio type of thing where you can select a particular SQL statement that you want to run manually when you're examining data and designing queries. We use SPs for all interactions with DB but I'm talking more about a code reuse type of thing. I guess I could create a library of SPs and then run those when I need to, but I'd still need a way to manage them with names and stuff so I could remember what they do. I guess it is really because I'm in the midst of designing queries for a large system and I only have to do this every 2 or 3 years so I have a long time away from it.
Quote:
I mean like a development studio type of thing where you can select a particular SQL statement that you want to run manually when you're examining data and designing queries.
MySQL Workbench allows that, including loading and saving the files just like an IDE, keeping files in different tabs, etc.
-
Sounds like templates: Template Explorer - SQL Server Management Studio (SSMS) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
That does look good but...
Link provided said
Important This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
I didn't know that, thanks for the info. I have another one: [Download and install Azure Data Studio - Azure Data Studio | Microsoft Docs](https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15) Maybe this does what you want? I think it also works with SQL Servers that are not in Azure but I didn't test that.
Check out my blog at http://msdev.pro/
-
A lot of tools come to mind on this topic. Notepad, or Notepad++: I recommend using "AGENT RANSACK" a very fast multi-threaded Search engine. (You can start the search in your ROOT file for the SQL files) (Side note: This is my number one favorite tool) Organize the SQL files, by INDEXING Them (aka: "Tags") INDEX: (Example) #DATE Ex: 01/01/2021 #PROJECT Ex: (NAME) #TYPE Ex: (SELECT INSERT UPDATE DELETE) #NOTATION Ex: That query for the thing I had to do at 1AM ...more as needed ---------------------------------------------------------------- Open "Agent ransack" and search on terms in the sql. This could be all put in a database, but I think that may be a bit too much. You want something simple, and you want to find it fast. You already have a body of SQL that is not indexed. Start indexing all NEW SQL FILES, and as you revisit the old ones, (that are not indexed), Index them as you go along. Keep it all in one file, or multiple files, doesnt matter much with Agent Ransack. So what you wind up with is your very own Google Search for your sql. Keep It Simple, keep it moving.
-
Just a thought: Use a library like JsonQL to convert SQL to a JSON representation and store them in MongoDB with descriptive metadata. Search on the content and/or metadata.
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
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.
-
There is no good way to store good SQL queries for future use. Just, no good way. :rolleyes:
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.
-
Quote:
I mean like a development studio type of thing where you can select a particular SQL statement that you want to run manually when you're examining data and designing queries.
MySQL Workbench allows that, including loading and saving the files just like an IDE, keeping files in different tabs, etc.
-
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.
