SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Convert select statements in scripts into Inline-TVF's automatically

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5...

In-line TVF's are awesome, they are a great way to reuse T-SQL code without the performance penalties of multi line TVF's or horror of horrors Scalar Functions. Taking select statements and turning them into in-line TVF's is actually really really simple using the TSql ScriptDom so here is another tool for the SSDT Dev Pack that lets you highlight a select statement in SSDT and generate a TVF and replace the statement with a call to the TVF.

It isn't perfect, I totally ignore parameters and if you have a join and only want to pull out one of the statements you will need to do it manually but this will help in other situations and be a good start by creating a TVF using the correct form (returns table as select ...).

So for example if we have this code:

highlight select statement in SSDT

If you choose Tools-->SSDT Dev Pack-->Extract into TVF (or do the right thing and map CTRL+K,CTRL+E) and you are given the following dialog which lets you give the function a name and a location you want it to be created in SSDT:

choose a name and location for the new tvf

Finally, the TVF is created and the original statement is replace with a call to the function (whoop whoop):

the tvf is created and the original statement changed to call the tvf

Hopefully someone finds this useful, I know it is good for me :)

To grab the devpack get it from:

https://the.agilesql.club/Projects/SSDT-Dev-Pack

Ed Elliott's Sql Developer Blog

Ed is a Sql developer who has a mixed background in support, as a dba and as a developer working with a number of languages c, c#, vb, go, assembly with a variety of technologies and is currently trying to make the sql developer community a little bit more agile, one build step at a time!

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...