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

First release of my own personal T-SQL Code Library on github

Like many (most???) T-SQL developers I keep a stash of useful code that I’ve garnered down the years because I know its all going to come in useful at some point in the future. It includes code I’ve written myself and also code that others have shared on their own blogs. For example my code library includes the following:

I’ve never seen the point of keeping one’s code library to one’s self, might as well share it in case anyone else might find it useful, so up to now I’ve kept my collection of scripts publicly available on SkyDrive (go see it if you like).

That’s all fine and dandy but I figured this could be improved. SkyDrive is a file sharing site and whilst it includes a nice code viewer/editor it is not an ideal solution for storing code, code should be stored in a version control system (e.g. Git, TFS, Subversion, etc..). I opted to make my code library available on Github at https://github.com/jamiekt/TSQLCodeLibrary/ because it provides:

  • file version history
  • releases
  • ability for anyone else to fork my code library and build upon it to maintain their own code library
  • lots of tools necessary for modern code development

and moreover all the cool kids seem to be using Github so I figured I’d give it a bash as well.

The code library exists as a collection of views, functions and stored procedures in an SSDT project. I’m a massive fan of SSDT so there were many reasons for my choosing to do this but the overriding reason was that SSDT provides a single binary (i.e. a dacpac file) containing the entire code library that can be distributed as easily as emailing the file to someone. Deploying a dacpac is pretty simple and so is a great method for sharing T-SQL code.

What’s in my T-SQL code library?

In this first release, not much. There are only nine objects though I hasten to add that this is only a first release and I have a backlog of stuff that I need to add in there. One of the many advantages of using SSDT is that it makes it easy to add extended properties to describe the objects and the code library includes a view that surfaces all of that extended property information:

SELECT schema_name,object_name,object_type,CodeLibraryDescription
FROM jt.[vwCodeLibraryDescriptions]

20140112TSQLCodeLibraryv0.1_objects

How do you install the code library?

Download the two binaries:

  • master.dacpac
  • TSQLCodeLibrary.dacpac

and store them together in a folder. Open a command prompt at that folder and type:

"%ProgramFiles(x86)%\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe"
 /action:Publish
 /SourceFile:TSQLCodeLibrary.dacpac
 /TargetServerName:"<your_sql_instance>"
 /TargetDatabaseName: <prefered_database_name>

(replacing <your_sql_instance> with the name of the SQL Server instance where you want to create the code library and <prefered_database_name> with whatever you want the database to be called. Get rid of the line feeds as well, they are just used here for clarity)

This will create a SQL Server database containing my code library:


20140112DeployedTSQLCodeLibraryv0.1

If any of the code in my code library proves useful to you then that’s great however my wish here is that some of you other folk out there feel motivated to share your own code in a similar manner. If you do so please post a comment below and let me know.

@Jamiet

Published Sunday, January 12, 2014 11:41 PM by jamiet

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

 

Rui Romano said:

January 13, 2014 3:46 AM
 

Eric Lawson said:

Thanks you very much Jamie.

January 13, 2014 11:35 AM
 

Joe Harris said:

Nice work. I really should get my stuff up on GitHub as well.

Needs a fairly deep check to make sure any client references are removed though. :(

January 14, 2014 6:20 AM
 

Neeraj Mittal said:

Good Job !! really helpful.

January 16, 2014 10:26 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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