Get the T-SQL inputs & outputs using a dacpac?

Question

FSharp.Data.SqlClient relies on sys.sp_describe_first_result_set to discover the schema of a query's result set. The problem is that this requires a connection to the SQL Server database at design/build time. Is it possible to get this information from a .dacpac? Data-tier Applications claim to "enable declarative database development".

There are libraries to read the dacpac and its contents and the T-SQL AST. I would think the input and output types could be derived from that info.

// C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.dll
// C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Microsoft.SqlServer.Dac.Extensions.dll
// C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

open Microsoft.SqlServer.Dac
open Microsoft.SqlServer.Dac.Model
open Microsoft.SqlServer.TransactSql.ScriptDom

Is deriving a list of the inputs and output from the declarative model possible/reliable? Is there a good place to log a feature request? :-)

Answer 1

For simple queries (select * from table etc) it is straightforward, use the scriptdom to get the column names and tables - I have an example in an old blog post to get the column types and enumerate tables:

https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-api-getting-column-type-information/

This shows you an example of using the scriptdom, it is pretty straightforward once you understand you have to use the visitor pattern to get to the bits you need:

http://blogs.msdn.com/b/arvindsh/archive/2013/04/04/using-the-transactsql-scriptdom-parser-to-get-statement-counts.aspx

The problem is that people can write strange t-sql that sql server happily works with and working out the actual intention of the code is difficult even with the api's available.

So working out what to do in all cases is hard (not impossible but hard), for example:

create procedure getdata
as

if exists(select * from schema.table where id = 999)
begin
  select 100 as id, 101 as number;
  return
end

select 'abc' as name, * from schema.another_table

What do you get - do you get 2 int values or a string and all the values on the table? I guess this is already a problem with the existing sql client though.

Something else that makes it harder is implicit conversions that happen in sql:

select 100, getdate() + '2014-01-01'

The ast will say you have a function and a string - you will need to get the return type of getdate and then know that adding a string to a date gives you a date - easy when you have a resultset, a bit harder when you just have the code.

If it is something that you do want to do then please don't be put off just bear in mind that there are some challenges!