Get Modified Objects As on Date

CREATE FUNCTION

[dbo].[fn_utl_GetModifiedObjectsByDate]

(
      @sdtInputDate           SMALLDATETIME,

      @btShowViews            BIT,

      @btShowSPs              BIT

)

RETURNS @tblModifiedObjectsAsOnDate
TABLE

(      ObjectType              VARCHAR(20), 

      ObjectName              VARCHAR(500), 

      CerateDate              DATETIME,

      ModifiedDate            DATETIME,

      Script                        VARCHAR(MAX) 

)

BEGIN

 

      IF @btShowSPs = 1

      BEGIN

            INSERT            @tblModifiedObjectsAsOnDate 

            SELECT            'Stored Procedure', P.name, P.create_date, 

                              P.modify_date, M.definition

            FROM        sys.sql_modules
M

                              INNER JOIN sys.procedures
P ON M.object_id = P.object_id

                              AND CONVERT(SMALLDATETIME, CAST(DATEPART(MONTH, P.modify_date)AS VARCHAR(2)) + '-' + CAST(DATEPART(DAY, P.modify_date) AS VARCHAR(2)) + '-' + CAST(DATEPART(YEAR, P.modify_date)AS VARCHAR(4)), 101) = @sdtInputDate 

      END

 

      IF @btShowViews = 1

      BEGIN

            INSERT            @tblModifiedObjectsAsOnDate 

            SELECT            'View', V.name, V.create_date, V.modify_date, M.definition

            FROM        sys.sql_modules
M

                              INNER JOIN sys.views V ON M.object_id = V.object_id

                              AND CONVERT(SMALLDATETIME, CAST(DATEPART(MONTH, V.modify_date)AS VARCHAR(2)) + '-' + CAST(DATEPART(DAY, V.modify_date) AS VARCHAR(2)) + '-' + CAST(DATEPART(YEAR, V.modify_date)AS VARCHAR(4)), 101) = @sdtInputDate 

      END

 

      RETURN      

END