Accessing query execution plan results programmatically

Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample

SET SHOWPLAN_XML ON; query... SET SHOWPLAN_XML OFF;

but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.

Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.

I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.

Thanks -- MT

C# sample

SqlConnectionStringBuilder connString = new SqlConnectionStringBuilder();
connString.ApplicationName = "TryIt";
connString.DataSource = @."(local)";
connString.IntegratedSecurity = true;
connString.InitialCatalog = "tempdb";
using (SqlConnection conn = new SqlConnection(connString.ToString()))
{
conn.Open();
SqlCommand showplan_on_cmd = new SqlCommand("set showplan_xml on", conn);
showplan_on_cmd.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("Select count(*) From sys.tables", conn);
using (SqlDataReader showplan_results = cmd.ExecuteReader())
{
if (showplan_results.Read())
{
Console.WriteLine(showplan_results[0].ToString());
}
}
SqlCommand showplan_off_cmd = new SqlCommand("set showplan_xml off", conn);
showplan_on_cmd.ExecuteNonQuery();
}

Hope that helps.

PS: You can use this sample inside a .NET assembly with slight modifications (use a context-connection) to do the same thing inside a stored procedure.

|||That worked great. I had tried an ExecuteDataSet operation, but I had not thought to try a data reader. Thanks!