How do you store and retrieve useful scripts?

Question

How do you store and retrieve useful scripts?

MikeRen

Ten Centuries

Points: 1295
More actions
January 4, 2018 at 2:21 am

#409917

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

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

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

Hamid-Sadeghian SSC Eights! Points: 890 More actions · Answer 1

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.

HappyGeek SSCoach Points: 18729 More actions · Answer 2

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

...

Clive Strong SSChampion Points: 11222 More actions · Answer 3

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.

martinkuen SSC Rookie Points: 40 More actions · Answer 4

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

MikeRen Ten Centuries Points: 1295 More actions · Answer 5

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?

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 6

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

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

below86 SSChampion Points: 11532 More actions · Answer 8

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.

MikeRen Ten Centuries Points: 1295 More actions · Answer 9

@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.

below86 SSChampion Points: 11532 More actions · Answer 10

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.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 11

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 🙂

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

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

HappyGeek SSCoach Points: 18729 More actions · Answer 13

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 🙂

...

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 14

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