How do you store and retrieve useful scripts?

  • Hello,
    During my time as a DBA I find that I collect useful scripts to help me identify data, deadlocks, security, etc. but I'm often searching the internet for them as I can never find the one I want when I need it. Does anyone out there have a way that works for them of storing useful scripts so that they can be found again easily when required?
    Thanks

  • Hi,
    I create a monitoring database and create SP for each scripts that I need for monitoring servers.
    For updating monitoring DB also create a database project in VS 2015  and add to TFS source control.
    for each SP that must be run also create a table with capture of running script date that can compare or create a baseline for each server.

  • Have used GitHub for version control in the past along with Confluence for documentation.

    ...

  • I always used to store them on USB, then Google Drive.  However, I have since created a private github repository for all my scripts.  This way, they are accessible whether I am at home or in the office.  I pay for the service, so the repository is private.

    All work specific queries are stored securely on a network volume with restricted access.

  • I'm storing them in a visual studio online project. There is a wiki plugin you could use for documentation.

  • martinkuen - Thursday, January 4, 2018 3:21 AM

    I'm storing them in a visual studio online project. There is a wiki plugin you could use for documentation.

    Sounds promising. How do you annotate/group them so that you can easily find them again?

  • Hi,
    we use suberversion. Every time I start the SSMS, the  scripts are copied from subversion to the template folder in the SSMS.
    So my colleagues and I have the same scripts local in the SSMS, easy to use.
    Kind regards,
    Andreas

  • A combination of source control (flavor of the month is Git and GitHub) and the Snippets in SQL Prompt. Source control ensures I have backups in a central location as well as versioning, etc. and Snippets makes it easy to call the things up as I need them.

    Also, for some queries, such as Jeff's split function, that kind of thing, you put them in a central database so they're available for any other database.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I've been using OneNote, I created a section to hold all of these scripts.  Each script is on a separate page within the section, I try to give each page a meaningful name.  OneNote takes care of versioning for me.  And it's easily searchable.  I've shared this section with others on my team.  It seems to be working for us at this time, we are developers not DBA's.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • @below86, that's a nice simple solution which I think I can make use of in my shop. Thanks for the idea.
    I could investigate GitHub, Subversion or Monitoring DB but they'd all require some level of investigation and setup whereas OneNote is already common in my shop and my team all know how to use it. I can see that the number of pages would increase significantly over time but OneNote is pretty good at searching and I can always group similar scripts into folders.

  • mike.dinnis - Thursday, January 4, 2018 8:08 AM

    @below86, that's a nice simple solution which I think I can make use of in my shop. Thanks for the idea.
    I could investigate GitHub, Subversion or Monitoring DB but they'd all require some level of investigation and setup whereas OneNote is already common in my shop and my team all know how to use it. I can see that the number of pages would increase significantly over time but OneNote is pretty good at searching and I can always group similar scripts into folders.

    Sometimes the simple answer is the best, IMHO.
    A former coworker turned me on to OneNote about 4 years ago.  I love it!!  I used to save all of these snippets or scripts into .sql files in 'My Documents'.  It was always a pain to find what I needed.  Many times I named the file something I thought made sense at the time but later it didn't always stand out.  Happens a little with OneNote in naming pages, but it's easier to search.

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • I use a very sophisticated system: my frequently used queries are in a text file, with leading comments including a name/title for the query, and I use Ctrl+F 🙂

  • grant@scarydba.com - Thursday, January 4, 2018 5:59 AM

    A combination of source control (flavor of the month is Git and GitHub) and the Snippets in SQL Prompt. Source control ensures I have backups in a central location as well as versioning, etc. and Snippets makes it easy to call the things up as I need them.

    Also, for some queries, such as Jeff's split function, that kind of thing, you put them in a central database so they're available for any other database.

    I would recommend this. You don't even need SQL Prompt (although it might make things easier). You can use snippets on any recent SSMS http://www.sqlservercentral.com/articles/SSMS/138994/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • No one has suggested that SSC is a good place to store generic, re-usable scripts so would like to suggest that this would be a far more sociable media; I have found many useful script here and think highly of the authors, you know who you are, so find SSC as the place to go for reminding myself of those scripts (and finding them again). Never tried the SQL Script add-in available on this site or understand what is meant by the favourites folder, anyone else know? Incidentally it seems to be not compatible with SQL 2016 or 2017, are there plans to upgrade at all? This should be more promoted IMHO 🙂

    ...

  • Red Gate SQL Source Control works almost with all source control systems - https://www.red-gate.com/products/sql-development/sql-source-control/

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

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