THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Take your script library with you [T-SQL]

As SQL professionals I’m sure many of you build up a library of SQL scripts that you take from project to project, right? Here’s a tip for those of you that have such a collection and want to make sure that you always have ready access to it.

I’m a big fan of all things sync-related and there are many tools on the market now that will sync the contents of a Windows folder to the cloud or around different machines; Dropbox is a popular one, as is Windows Live Mesh which I use and I also hear good things about SugarSync (they’re all free by the way). It doesn’t matter which one you use, the basic premise of these tools is that any file dropped into a monitored folder automatically gets copied up to a cloud store and whenever you buy/inherit a new machine simply installing the client will bring that stuff down for you. In short, its an ideal solution for always having access to your SQL script library.

Here’s a screenshot of the Windows Live Mesh client:

Windows Live Mesh client

Notice that I have a folder called “Personal SQL Store” (I also have one for my Powershell scripts too). Its synced to two of my laptops and also to SkyDrive Synced Storage which is Windows Live’s name for the cloud store that I referred to earlier. Up at that cloud store I have ready access to the contents of that folder:

Skydrive synced storage

It makes it really simple to get to those folders from any computer in the world (although admittedly I have occasionally come a cropper thanks to some over zealous IT departments) and I’d highly recommend using a similar system if you have such a script library that you always need access to particularly if, like me, you’re always on the move around different client sites. Alternatively if you don’t have your own set of scripts I have the option to share my folder with up to nine people so if you want read-only access to my script library let me know.

UPDATE: Adam Machanic posted a comment that made me realise it was pretty stupid to invite you to share my script library without telling you what was in it. Hence:

  • cdf.TitleCase - Returns Title Case Formatted String
  • dbo.FindNumberOfOccurencesOfOneStringInAnother
  • fnSplitWithCTE - take a delimited list of characters and return them as a dataset
  • All Sessions
  • Data cache usage per table
  • DatawarehouseCleardown - Deletes all data from all tables in a database, respecting FKs.
  • Discovering_tempdb_Stats – Various queries to return info about tempdb
  • Empty-SQL-Cache
  • Get Index Fragmentation
  • Get Table Row Counts
  • Index information including column names
  • Paul Randal myth - DDL Triggers are INSTEAD OF triggers
  • Paul Randal myth - Null bitmap myth
  • Split csv into a dataset using XML & XQuery
  • SSISPackageStatsCollector_v2
  • tablestats – Stats for every table in a DB
  • View all permissions
  • csp_ToolRenameObjects2005 – Rename FKs, CHECK constraints, Indexes according to a defined naming convention. Work on SQL2005 and above
  • dbo.csp_ToolIDAssignSurrogateKey – Assign/Reassign surrogate keys on a table
  • sp_generate_inserts2005 – Generate inserts according to existing data in a table. Works on SQL2005 and above
  • SqlClrToolkit – a suite of useful SQLCLR procs/functions. FIRST/LAST/MERGE aggregate functions, SafeDivision, RegexMatch, CountString, CapitaliseText, RemoveWhitespace, DynamicPivotSQL, Split, DynamicPivot, DirectoryInfo, RegistryInfo, FindChildTables, EnableDisableConstraints
  • SSAS clearcache

Hopefully the names are self-explanatory enough!

UPDATE 2:

Jezza101 left a comment below suggesting that I just zip the whole lot up and post it somewhere which is what I have done at: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20101003/Personal%20SQL%20Store.zip

Hope this is useful!

UPDATE 3:

Seems I may have made a bad choice in choosing Live Mesh. I have managed to add a few people but now every time I add someone I get this:

image

I’m waiting on a reply from Microsoft but in the meantime, plesae don’t ask me to add you because I won’t be able to. I shall come back and update this as and when I am able.

@Jamiet

Published Sunday, October 03, 2010 6:48 PM by jamiet
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jason Brimhall said:

Thanks for the info - I may have to invest in the same kind of cloud folder.

October 3, 2010 1:11 PM
 

Adam Machanic said:

So what's in your script library?

October 3, 2010 2:10 PM
 

jamiet said:

Good point Adam. Updated it now.

October 3, 2010 4:34 PM
 

Jezza101 said:

I'm sure there are more than 9 people who'd like to download that set, can you zip it up and upload it somewhere?  Cheers.

October 4, 2010 7:04 AM
 

Oracle db said:

Gr8 program still need to work through it to get the hack of it.

October 4, 2010 9:15 AM
 

humble said:

Thank you for the post and the scripts!

October 8, 2010 3:22 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement