Unanswered: query the names of stored procedure results columns
So I am a little miffed about the diligence of the unit and QA testing of database changes lately because I deal with a lot of the production issues introduced by my team mates.
I have a little bit free time today at work, and I thought I might build a little engine that tests SPs that return result sets of stored procedures for my developers and does a perfomance number comparison. I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
... but I can not seem to figure out how to dynamically gather the schema of the result set. This is the level if testing that I apply myself to things that I am concerned about. I was just hoping to give a more push button tool to my developers.
If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.