To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- =============================================
-- Author: Muhammad Jawad
-- Create date: 2/3/2017
-- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases
-- =============================================
Alter PROC UP_FindAndBackupAllDatabaseHaveChanges
AS
    BEGIN
----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----
        DECLARE @loopStart INT= 1;
        DECLARE @looend INT;


        IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL
            BEGIN
                DROP TABLE #AllDatabaseTables;
            END;

        CREATE TABLE #AllDatabaseTables
            (
              ID INT IDENTITY(1, 1)
                     PRIMARY KEY ,
              ObjectID BIGINT ,
              TableName VARCHAR(500) ,
              DatabaseName VARCHAR(500)
            );

        DECLARE @temp TABLE
            (
              idx INT PRIMARY KEY
                      IDENTITY(1, 1) ,
              DBName VARCHAR(200)
            );
        INSERT  INTO @temp
                ( DBName
                )
                SELECT  '[' + name + ']'
                FROM    sys.databases
                WHERE   state = 0
                        AND name NOT IN ( 'distribution','tempdb' )
                ORDER BY 1;


        SET @looend = ( SELECT  MAX(idx)
                        FROM    @temp
                      );

        WHILE @loopStart <= @looend
            BEGIN

                DECLARE @Database VARCHAR(200);
                SET @Database = ( SELECT    DBName
                                  FROM      @temp
                                  WHERE     idx = @loopStart
                                );

                DECLARE @SQL NVARCHAR(MAX)= ( 'Use ' + @Database
                                              + '

INSERT INTO #AllDatabaseTables
        ( ObjectID ,
          TableName ,
          DatabaseName
        )
SELECT  US.object_id ,
        SO.name AS TableName ,
        DB_NAME() AS DatabaseName --case when count(distinct US.object_id) > 0 then 1 else 0 end as IS_CHANGED
FROM    sys.dm_db_index_usage_stats US
        INNER JOIN sys.objects SO ON SO.object_id = US.object_id
WHERE   database_id = DB_ID(DB_NAME())
        AND last_user_update BETWEEN CAST(CAST(CAST(GETDATE()-1 AS DATE) AS VARCHAR(100))
                                     + '' 00:00:00.000'' AS DATETIME)
                             AND     CAST(CAST(CAST(GETDATE()-1 AS DATE) AS VARCHAR(100))
                                     + '' 23:59:59.998'' AS DATETIME); ' );

                EXEC sp_executesql @SQL;
                SET @loopStart = @loopStart + 1;
            END;
----**************************END: To get all the tables and database names whose data have changed in last 24 hours **************************----
        IF OBJECT_ID('tempdb..#FinalTableAllDB') IS NOT NULL
            BEGIN
                DROP TABLE #FinalTableAllDB;
            END;

        CREATE TABLE #FinalTableAllDB
            (
              FID INT IDENTITY(1, 1)
                      PRIMARY KEY ,
              FDbName VARCHAR(200)
            );

        INSERT  INTO #FinalTableAllDB
                ( FDbName
                )
----**** To get database names that have data changes which we already got from above code.
                SELECT DISTINCT
                        DatabaseName
                FROM    #AllDatabaseTables
                UNION
----**** To get database names that have DDL changes
                SELECT  DISTINCT
                        DatabaseName
                FROM    TrackingDB..DDL_Events_Log
                WHERE   EventTime BETWEEN CAST(CAST(CAST(GETDATE() - 1 AS DATE) AS VARCHAR(100))
                                          + ' 00:00:00.000' AS DATETIME)
                                  AND     CAST(CAST(CAST(GETDATE() - 1 AS DATE) AS VARCHAR(100))
                                          + ' 23:59:59.998' AS DATETIME)
                        AND DatabaseName NOT IN ( 'distribution','tempdb' )
                        AND DatabaseName NOT LIKE '%ReportServer%'
                UNION
----**** To get Newly Created/Modified database names
                SELECT DISTINCT
                        name
                FROM    sys.databases
                WHERE   create_date BETWEEN CAST(CAST(CAST(GETDATE() - 1 AS DATE) AS VARCHAR(100))
                                            + ' 00:00:00.000' AS DATETIME)
                                    AND     CAST(CAST(CAST(GETDATE() - 1 AS DATE) AS VARCHAR(100))
                                            + ' 23:59:59.998' AS DATETIME);

--SELECT * FROM #FinalTableAllDB



----**************************Start: Backup All database with Cursor that have priovided by the above query **************************----

        DECLARE @name VARCHAR(50); -- database name
        DECLARE @path VARCHAR(256); -- path for backup files
        DECLARE @fileName VARCHAR(256); -- filename for backup
        DECLARE @fileDate VARCHAR(20); -- used for file name
        SET @path = 'E:\DB_Backup\2012\Full\';
        SELECT  @fileDate = CONVERT(VARCHAR(20), GETDATE(), 104);
        DECLARE db_cursor CURSOR
        FOR
            SELECT  FDbName
            FROM    #FinalTableAllDB;
           

        OPEN db_cursor;
        FETCH NEXT FROM db_cursor INTO @name;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @fileName = @path + @name + '_' + @fileDate + '_FULL.BAK';
                BACKUP DATABASE @name TO DISK = @fileName;

                --SELECT  'BACKUP DATABASE ' + @name + ' TO DISK = ' + @fileName;
                FETCH NEXT FROM db_cursor INTO @name;
            END;
        CLOSE db_cursor;
        DEALLOCATE db_cursor;

----**************************END: Backup All database with Cursor that have priovided by the above query **************************----

    END;