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.

Answer 2

After browsing the StoredProcedure Members page (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.storedprocedure_members.aspx) on MSDN, I see two possible solutions.

One, you can use GetTextBody to get the actual script for the stored procedure. After that you can somehow parse the stored procedures. Assuming they were created and can be modified by you or a member of your development team (assuming you have one) then this can be a little easier. If not, then it will get really messy, really fast and won't be 100%... so I don't REALLY recomend that.

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.

Edit: If you want to get just the parameters for an SP, that would be relatively easy to do. There is (according to the previous link) a collection called Parameters that you can access.