Sometimes a piece of technology passes you by completely, T4 templating happens to have done that for me. On tuesday night, at SQL Supper, Geoff Clark ( a uk MCM ) (t) done a fantastic presentation on end-to-end datawarehouse loading, a portion of this was on t4 templating. The question arose about SSDT and T4 templating and as a coincidence literally the next day the SSDT team put out the March 2014 release, having my interest already piqued i thought id have a play 
So… lets add a TSQL Template
That will now add base TSQL template into you project, however there is a slight snag , its broken 
This is confirmed and will be fixed in the next release as stated in this msdn forum thread. Bugger!!!
However, until then, all is not lost, although the DacFX portion is broken T4 templating still works so a more ‘generic’ solution is available. The basis of this is remarkably similar to TSQL Smells, iterate through a project adding the .sql files into a model and examining the DOM for interesting ‘stuff’.
After a bit of playing around, very much a T4 noob, this is the script I came up with:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 | <#@ template language="C#" debug="true" hostspecific="true" #> <#@ assembly name="Microsoft.VisualStudio.Shell.Interop.8.0" #> <#@ assembly name="EnvDTE" #> <#@ assembly name="EnvDTE80" #> <#@ assembly name="VSLangProj" #> <#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll" #> <#@ assembly name="C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.dll" #> <#@ assembly name="C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.Extensions.dll" #> <#@ import namespace="Microsoft.VisualStudio.Shell.Interop" #> <#@ import namespace="EnvDTE" #> <#@ import namespace="EnvDTE80" #> <#@ import namespace="Microsoft.VisualStudio.TextTemplating" #> <#@ import namespace="Microsoft.SqlServer.Dac" #> <#@ import namespace="Microsoft.SqlServer.Dac.Model" #> <#@ import namespace="System.IO" #> <#@ import namespace="System.Collections.Generic" #> <#@ output extension=".sql" #> -- Dynamic File generated by db <# var hostServiceProvider = (IServiceProvider)this.Host; var dte = (DTE)hostServiceProvider.GetService(typeof(DTE)); using (TSqlModel model = new TSqlModel(SqlServerVersion.Sql110, new TSqlModelOptions { })) { foreach(Project project in dte.Solution) { IterateThroughProject(project.ProjectItems,model); } List<TSqlObject> allTables = GetAllTables(model); foreach (var table in allTables) { #> --Table <#= table.Name.Parts[0] #>.<#= table.Name.Parts[1] #> <# } } #> -- File Done <#+ public List<TSqlObject> GetAllTables(TSqlModel model) { List<TSqlObject> allTables = new List<TSqlObject>(); var tables = model.GetObjects(DacQueryScopes.All, ModelSchema.Table); if (tables != null) { allTables.AddRange(tables); } return allTables; } private void IterateThroughProject(ProjectItems PrjItems,TSqlModel model) { foreach(ProjectItem PrjItem in PrjItems) { if(PrjItem.Name.EndsWith(".tt", StringComparison.OrdinalIgnoreCase)){ // Dont Load the files we want to build continue; } if(PrjItem.ProjectItems!=null) { IterateThroughProject(PrjItem.ProjectItems,model); } if(//PrjItem.Object.GetType().ToString() == "Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode" && PrjItem.Name.EndsWith(".sql", StringComparison.OrdinalIgnoreCase)) { #> --This is a sql file and will be processed --<#= PrjItem.FileNames[0] #> <#+ if (!PrjItem.Saved) { PrjItem.Save(); } StreamReader Reader = new StreamReader(PrjItem.FileNames[0]); string Script = Reader.ReadToEnd(); model.AddObjects(Script); } } } #> |
This should all be fairly self explanatory, we iterate through the project in the member function IterateThroughProject adding any found .sql files to the model. Then use the model.GetObjects member to find all tables in the model, iterate over that list printing schema and table name.
I have seen a few clunky TSQL generation routines written in TSQL but i think porting those into T4 templating and having them built directly into a dacpac will be a big boon.
Have fun
