Get result columns from a StoredProc without exec it, using Microsoft.SqlServer.Management.Smo

Question

I want to get a list of result sets and columns that i can expect from a SP. I have been able to get at the parameters, script... but i don't know where to get at the result sets and column names.

using Microsoft.SqlServer.Management.Smo;

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["mydbconn"].ConnectionString))
        {
            conn.Open();
            Server sv = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(conn));
            Database db = sv.Databases["mydb"];

            foreach (StoredProcedure sp in db.StoredProcedures)
            {
                string[] columns = sp.??????
            }

        }

Is there a way to get at the columns? I'm ultimately trying to write a code generator to automate my writing data access objects. Thanks SO!

EDIT: "Another solution is if you can get at the value of ScalarResult, you may be able to convert it into something useful that you can derive the columns from." : Any ideas how to get at that?

Answer 1

That is a known difficult area. Some systems try this by executing the stored procedure with SET FMTONLY ON enabled, but that has a range of problems (i.e. it will still execute some code, which can be bad). It is also notoriously hard to get the schema for non-trivial queries (for example, where different branches do different selects).

If you know that the procedures are side-effect-free, and don't SELECT in branches, then try the SET FMTONLY ON trick - but at least understand the possible impact.

For queries, in some ways table-valued-functions (UDFs) can be more practical, since the schema is formalised.