T-SQL - How to obtain column data programmatically from stored procedure with FMTONLY

Using set FMTONLY on; allows me to see the columns returned from a stored procedure in SSMS, but what if I want to programmatically grab those column names within a procedure and do something with them?

Basically, what I want to achieve is something like this...

declare @columns nvarchar(max) = (
    select name + ', '
    from sys.columns
    where object_id = OBJECT_ID('my_stored_procedure')
    for xml path(''));

Obviously, that doesn't work. Is this possible with T-SQL?

Answers


OPENQUERY is one possible solution, with FMTONLY OFF. Load the exec results into a dummy table, and build it's column structure into a string. The following demonstrates this method in sproc form. An assumption is that each sproc analyzed returns only one rowset.

USE AdventureWorks
GO

CREATE PROCEDURE MySprocCols
@ExecStr NVARCHAR(1000)
AS
SET FMTONLY OFF;
SET NOCOUNT ON;

IF EXISTS (
    SELECT 1
    FROM sys.objects o
    INNER JOIN sys.schemas s ON s.schema_id=o.schema_id
    WHERE o.[type]='U'
    AND s.name='dbo'
    AND o.name='MyCols'
)
DROP TABLE dbo.MyCols;

DECLARE @SQL NVARCHAR(1000)
SET @SQL = '
SELECT TOP(0) * 
INTO dbo.MyCols
FROM OPENQUERY(MyServer,'''+@ExecStr+''')';
EXEC SP_EXECUTESQL @SQL;

DECLARE     @Cols VARCHAR(MAX) = '';
DECLARE     @Del varchar(10) = ',';

SELECT      @Cols += @Del + c.name + ''
FROM        sys.objects o
JOIN        sys.schemas s           ON s.schema_id=o.schema_id
JOIN        sys.columns c           ON c.object_id=o.object_id
WHERE       o.[type]='U'
            AND s.name='dbo'
            AND o.name='MyCols'
ORDER BY    c.column_id

SET @Cols = STUFF(@Cols,1,LEN(@Del),'');
PRINT @Cols;
GO

EXEC dbo.MySprocCols 'exec AdventureWorks.dbo.uspGetBillOfMaterials 746,''''9/9/2000''''';
--                          ^Must specify DB name here                    ^ Double-esc ^ all embedded single quotes

-- If you get an error like this:
--Msg 7411, Level 16, State 1, Line 2
--Server 'MyServer' is not configured for DATA ACCESS

-- ..you will first need to enable 'Data Access' through the following Surface Area Config command:
--exec sp_serveroption [MyServer],'Data Access','TRUE'

Sample execution result:

ProductAssemblyID,ComponentID,ComponentDesc,TotalQuantity,StandardCost,ListPrice,BOMLevel,RecursionLevel


Need Your Help

How to insert an image into an Access OLE field via .NET

c# database image ms-access ole

I have an Access .mdb database and I want to insert an image from an application developed in visual C# 2010. Pictures are stored in the database in the field of OLE-object.