Sql-server – ny way to identify which SP return data

This works locally for me to identify result returning procs using sp_describe_first_result_set, which is only available on SQL Server 2012+. It's on the quick/dirty side of things, but hopefully it'll get you started,

Use [Yourmom]

CREATE TABLE #commands
     (
         ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
         CurrentObject sysname,
         Command NVARCHAR(2000)
     );

CREATE TABLE #success
     (
         object_name sysname NULL,
         is_hidden BIT NULL,
         column_ordinal INT NULL,
         name sysname NULL,
         is_nullable BIT NULL,
         system_type_id INT NULL,
         system_type_name sysname NULL,
         max_length SMALLINT NULL,
         precision TINYINT NULL,
         scale TINYINT NULL,
         collation_name sysname NULL,
         user_type_id INT NULL,
         user_type_database sysname NULL,
         user_type_schema sysname NULL,
         user_type_name sysname NULL,
         assembly_qualified_type_name NVARCHAR(4000) NULL,
         xml_collection_id INT NULL,
         xml_collection_database sysname NULL,
         xml_collection_schema sysname NULL,
         xml_collection_name sysname NULL,
         is_xml_document BIT NULL, 
         is_case_sensitive BIT NULL, 
         is_fixed_length_clr_type BIT NULL, 
         source_server sysname NULL, 
         source_database sysname NULL, 
         source_schema sysname NULL,
         source_table sysname NULL,
         source_column sysname NULL,
         is_identity_column BIT NULL,
         is_part_of_unique_key BIT NULL, 
         is_updateable BIT NULL, 
         is_computed_column BIT NULL, 
         is_sparse_column_set BIT NULL, 
         ordinal_in_order_by_list SMALLINT NULL,
         order_by_is_descending SMALLINT NULL,
         order_by_list_length SMALLINT NULL, 
         tds_type_id INT NULL,
         tds_length INT NULL,
         tds_collation_id INT NULL,
         tds_collation_sort_id INT NULL
     );

CREATE TABLE #fail (
                    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    CurrentObject sysname,
                    Command NVARCHAR(2000),
                    Error_Line INT,
                    Error_Message NVARCHAR(4000),
                    Error_Number INT,
                    Error_Procedure sysname,
                    Error_Severity INT,
                    Error_State INT 
                    );

DECLARE @CurrentObject sysname;
DECLARE @CurrentCommand NVARCHAR(2000);

INSERT INTO #commands ( CurrentObject, Command )
SELECT OBJECT_NAME(sm.object_id),
       N'EXEC sys.sp_describe_first_result_set @tsql = N''EXEC ' + OBJECT_NAME(sm.object_id) + N''';'
FROM   sys.sql_modules AS sm
WHERE  OBJECT_NAME(sm.object_id) IS NOT NULL
AND    sm.definition LIKE '%CREATE%PROC%';

DECLARE result_cursor CURSOR FOR
SELECT CurrentObject, Command FROM #commands

OPEN result_cursor
FETCH NEXT FROM result_cursor into @CurrentObject, @CurrentCommand
WHILE @@FETCH_STATUS = 0
BEGIN 

 BEGIN TRY

    PRINT @CurrentCommand;
    INSERT #success (
                             is_hidden,
                             column_ordinal,
                             name,
                             is_nullable,
                             system_type_id,
                             system_type_name,
                             max_length,
                             precision,
                             scale,
                             collation_name,
                             user_type_id,
                             user_type_database,
                             user_type_schema,
                             user_type_name,
                             assembly_qualified_type_name,
                             xml_collection_id,
                             xml_collection_database,
                             xml_collection_schema,
                             xml_collection_name,
                             is_xml_document,
                             is_case_sensitive,
                             is_fixed_length_clr_type,
                             source_server,
                             source_database,
                             source_schema,
                             source_table,
                             source_column,
                             is_identity_column,
                             is_part_of_unique_key,
                             is_updateable,
                             is_computed_column,
                             is_sparse_column_set,
                             ordinal_in_order_by_list,
                             order_by_is_descending,
                             order_by_list_length,
                             tds_type_id,
                             tds_length,
                             tds_collation_id,
                             tds_collation_sort_id
                         )
    EXEC(@CurrentCommand);

END TRY

BEGIN CATCH

        INSERT #fail (
                      CurrentObject,
                      Command,
                      Error_Line,
                      Error_Message,
                      Error_Number,
                      Error_Procedure,
                      Error_Severity,
                      Error_State
                     )

        SELECT @CurrentObject,
               @CurrentCommand,
               ERROR_LINE(),
               ERROR_MESSAGE(),
               ERROR_NUMBER(),
               ERROR_PROCEDURE(),
               ERROR_SEVERITY(), 
               ERROR_STATE();


END CATCH;

    UPDATE #success
    SET object_name = @CurrentObject
    WHERE object_name IS NULL

FETCH NEXT FROM result_cursor into @CurrentObject, @CurrentCommand
END

CLOSE result_cursor
DEALLOCATE result_cursor

SELECT *
FROM #commands AS c

SELECT *
FROM #success AS s

SELECT * 
FROM #fail AS f

DROP TABLE #commands, #fail, #success