I noticed recently that if you use SQL Server Management Studio to script out a Select statement from a table-valued function, SSMS generates a Select *. In the following screen shot, I'm scripting out a Select statement from a table-valued function that's in the AdventureWorks2012 database (this TVF is just an example, not my real function).
In my situation, the Select statement being generated from my inline table-valued function will be going into an SSIS package as the source query for the data flow. A Select * is generally not thought of as a good thing in most cases anyway, but in an SSIS package usage of a Select * does introduce risk. If a new column gets added, deleted, or changed in database object - but not in the corresponding SSIS package - then the SSIS package will error out. If your change control processes and regression testing procedures ensure that the function and the SSIS package will always remain in conjunction with each other, then maybe the Select * isn't a big deal. However, ever since my phone rang in the middle of the night about 7 years ago because I made this exact mistake, my preference has been to reduce the risk of the Select * by specifying the columns going into the SSIS pipeline.
So, at this point I was (a) curious about why SSMS scripts out the Select * the way it does for a table-valued function, and (b) thinking about how to get all the individual columns scripted out from my inline table-valued function in a quick way. Here's where the super duper awesome SQL community comes in. I asked about this SSMS behavior on Twitter with the #sqlhelp tag. Robert Davis (@SQLSoldier) responded that a table-valued function is a routine, like a procedure, and so the output can be variable. This was good insight for me. And MartinSmith (@MartinSmith_) gave me a handy dandy tip about using an SSMS shortcut to script out the Select statement with columns specifically named.
Highlight the "Select * From TVF()" in the SSMS query window and then "Ctrl + Shift + Q" as shown in the following screen shot. The Query Designer window will pop up. Just copy the new Select statement from the Query Designer window.
And, with that SSMS shortcut, we have a quick way to get the full "Select Column1, Column2, Column3 From TVF()" for use as the source query in SSIS (or whatever other purpose).