October 27, 2008 at 12:54 pm
Hello everyone. Got a question that I am not sure if anyone has ever done before. I have a crazy stored procedure that depending upon input will return 1 of 14 different reports from different tables, etc. My manager has asked me to create a data dictionary for the procedure. While I could sit and hand copy every column from every report and reference the table and so on and so forth, I am trying to figure it out how to do it automatically.
So how does one get the column name, data type, description, etc from the output of a stored procedure where the tables that are being reference and the columns being returned are different for every option that is sent into the stored procedure.
Example:
Execute Stored Procedure
RESULT SET
ColumnHeader1, ColumnHeader2, ColumnHeader3…….
ColumnData1, ColumnData2, ColumnData3…….
From this result set, I need to get something like:
Name DataType Length/Bytes Description
ColumnHeader1 int 8 CustomerID
ColumnHeader2 nvarchar 100 Customer Name
ColumnHeader3 nvarchar 100 Customer Address
Does anyone have any ideas on how this can be done?
Thanks for all of the assistance.
Fraggle
October 27, 2008 at 1:09 pm
Check out the SET FMTONLY ON command. It won't return the column types and such, but it will get you all the column names.
http://msdn.microsoft.com/en-us/library/ms173839.aspx
You could also insert the output into a table and then extract the information out of syscolumns.
I have a feeling there will be a better way than either of these though.
October 27, 2008 at 2:42 pm
Well that is a start. That gets me all of the column headers. Now how to I get the properties for all of those columns and expoert those results?
In general, looking for similar results to the sys.columns result set.
Thanks,
Fraggle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply