USE msdb
GO
BEGIN TRANSACTION
    BEGIN TRY
        DECLARE @collection_set_id_15 int
        DECLARE @collection_set_uid_16 uniqueidentifier
        EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]   @name = N'Stored Procedure Usage - "ElamedDB" database',
                                                                    @collection_mode = 0, -- cached mode
                                                                    @description = N'Collects information about stored procedures use.',
                                                                    @logging_level = 1,
                                                                    @days_until_expiration = 30,-- set to the number of days you need to keep the history
                                                                    @schedule_name = N'CollectorSchedule_Every_30min', -- collector upload schedule
                                                                    @collection_set_id = @collection_set_id_15 OUTPUT,
                                                                    @collection_set_uid = @collection_set_uid_16 OUTPUT

        SELECT
            @collection_set_id_15,
            @collection_set_uid_16

        DECLARE @collector_type_uid_19 uniqueidentifier
        SELECT
            @collector_type_uid_19 = collector_type_uid
        FROM [msdb].[dbo].[syscollector_collector_types]
        WHERE Name = N'Generic T-SQL Query Collector Type';

        DECLARE @collection_item_id_20 int
        EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]  @name = N'Stored Procedure Usage Statistics',
                                                                    @parameters = N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
SELECT  d.object_id ,
        d.database_id AS [db_id],
        db_name(d.database_id) AS [db_name],
        OBJECT_NAME(object_id, database_id) AS [sp_name],
        d.last_execution_time,
        d.execution_count
FROM sys.dm_exec_procedure_stats AS d
WHERE d.database_id = DB_ID(''ElamedDB'') </Value>
 <OutputTable>sp_usage_stats</OutputTable></Query>
 <Databases><Database>_DemoDB</Database></Databases></ns:TSQLQueryCollector>',
                                                                    @collection_item_id = @collection_item_id_20 OUTPUT,
                                                                    @frequency = 5,
                                                                    @collection_set_id = @collection_set_id_15,
                                                                    @collector_type_uid = @collector_type_uid_19
        SELECT
            @collection_item_id_20

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    DECLARE @ErrorMessage nvarchar(4000);
    DECLARE @ErrorSeverity int;
    DECLARE @ErrorState int;
    DECLARE @ErrorNumber int;
    DECLARE @ErrorLine int;
    DECLARE @ErrorProcedure nvarchar(200);
    SELECT
        @ErrorLine = ERROR_LINE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
    RAISERROR (14684, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity,
    @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

END CATCH;

GO
USE msdb;
GO
DECLARE @collection_set int

SELECT
    @collection_set = collection_set_id
FROM dbo.syscollector_collection_sets
WHERE Name = 'Stored Procedure Usage - "ElamedDB" database'