问当数据库联机时运行存储过程

EN

CREATE TRIGGER [TrackDBStarted] 
ON ALL SERVER
FOR CREATE_DATABASE, ALTER_DATABASE

CREATE EVENT SESSION [TrackDBStarted] 
ON SERVER
ADD EVENT sqlserver.database_started
( ACTION ( sqlserver.database_name ) )

USE MASTER;
GO

IF ( OBJECT_ID( N'dbo.STARTUPPROC' ) IS NOT Null )
    DROP PROCEDURE [dbo].[STARTUPPROC];
IF ( OBJECT_ID( N'dbo.CHECKDBS' ) IS NOT Null )
    DROP PROCEDURE [dbo].[CHECKDBS];
GO

/* Create the stored procedure that will check for any new databases. */
CREATE PROCEDURE [dbo].[CHECKDBS]
AS
    SET NOCOUNT ON;

    DECLARE @Database   sysname;

    DECLARE c_databases CURSOR LOCAL FOR    SELECT name FROM sys.databases 
                                            WHERE name NOT IN ( N'master', N'model', N'msdb', N'tempdb' )
                                            AND name NOT IN ( SELECT [Database] FROM ##DBList )
                                            AND state = 0;  -- Online

    OPEN c_databases;
    FETCH NEXT FROM c_databases INTO @Database;

    WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        IF ( HAS_PERMS_BY_NAME( @Database, 'DATABASE', 'ANY' ) = 1 )  
        BEGIN

            /**************************************
            *** Do database related tasks here. ***
            ***************************************/

            INSERT INTO ##DBList ( [Database] )
            VALUES ( @Database );
        END

        FETCH NEXT FROM c_databases INTO @Database;
    END

    CLOSE c_databases;
    DEALLOCATE c_databases;
GO

/* 
Create the stored procedure that will:
1. Create the temporary table so it doesn't go out of scope. 
2. Create the job that will periodically call the CHECKDBS stored procedure.
*/
CREATE PROCEDURE [dbo].[STARTUPPROC]
AS
    SET NOCOUNT ON;

    -- Create the temporary table if it doesn't already exist.
    IF ( OBJECT_ID( N'tempdb..##DBList' ) IS Null )
    BEGIN
        CREATE TABLE ##DBList
        (
            [Database]  sysname NOT NULL,                       -- Name of the database.
            PRIMARY KEY CLUSTERED 
            (
                [Database] ASC
            )
        );
    END

    DECLARE @JobName    sysname         = N'DBCheck';       -- Name of the job.
    DECLARE @ServerName nvarchar(30)    = @@SERVERNAME;     -- SQL Server on which this job will be configured.
    DECLARE @JobDate    nvarchar(8)     = CONVERT( nvarchar(8), SYSDATETIME(), 112 );   -- Job start date.
    DECLARE @Command    nvarchar(max)   = N'EXEC [dbo].[CHECKDBS]';                     -- The T-SQL command to run in the step.

    -- Check if the job exists and create it if it doesn't.
    IF NOT EXISTS ( SELECT Null FROM msdb.dbo.sysjobs WHERE name = @JobName )
    BEGIN
        -- Add the job.
        EXEC msdb.dbo.sp_add_job 
            @job_name = @JobName;

        -- Add the job step.
        -- See https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql?view=sql-server-2017
        EXEC msdb.dbo.sp_add_jobstep
            @job_name = @JobName,
            @step_name = N'Job Step',
            @subsystem = N'TSQL',
            @command = @Command;

        -- Schedule the job.
        -- See https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobschedule-transact-sql?view=sql-server-2017
        EXEC msdb.dbo.sp_add_jobschedule @job_name = @JobName,
            @name = N'Minute',                  -- Name of the job schedule.
            @enabled = 1,                       -- Will be enabled.
            @freq_type = 4,                     -- Daily.
            @freq_interval = 1,                 -- Every x days.
            @freq_subday_type = 4,              -- Minutes.
            @freq_subday_interval = 1,          -- Sub day interval.
            @freq_relative_interval = 0,        -- Only used when @freq_type = 32 so set to 0.
            @freq_recurrence_factor = 0,        -- Not used for @freq_type = 4 so set to 0.
            @active_start_date = @JobDate,      -- Date on which job execution should commence, must be greater than or equal to 19900101.
            @active_end_date = 99991231,        -- No end date.
            @active_start_time = 0,             -- Start job at 00:00:00.
            @active_end_time = 235959;          -- End job at 23:59:59.

        -- Add the job to the SQL Server.
        EXEC msdb.dbo.sp_add_jobserver
            @job_name = @JobName,
            @server_name = @ServerName;
    END
GO

/* Mark the stored proc for startup. */
IF ( OBJECT_ID( N'dbo.STARTUPPROC' ) IS NOT Null )
    AND NOT EXISTS ( SELECT Null FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'STARTUPPROC' AND OBJECTPROPERTY( OBJECT_ID( ROUTINE_NAME ), 'ExecIsStartup' ) = 1 )
    EXEC SP_PROCOPTION N'STARTUPPROC', 'STARTUP', 'ON';
GO

USE MASTER;
GO
SELECT * FROM SYS.CONFIGURATIONS WHERE NAME = N'scan for startup procs';
SELECT * FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = N'STARTUPPROC' AND OBJECTPROPERTY( OBJECT_ID( ROUTINE_NAME ), 'ExecIsStartup' ) = 1;
SELECT * FROM msdb.dbo.sysjobs WHERE name = N'DBCheck';
SELECT * FROM sys.procedures WHERE name IN ( N'STARTUPPROC', N'CHECKDBS' );
SELECT * from tempdb.sys.objects WHERE name = N'##DBList';
GO

USE MASTER;
GO

-- Delete the job.
IF EXISTS ( SELECT Null FROM msdb.dbo.sysjobs WHERE name = N'DBCheck' )
    EXEC msdb.dbo.sp_delete_job @job_name = N'DBCheck';
GO

-- Remove the stored procedure and turn off the auto-startup.
IF EXISTS ( SELECT Null FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = N'STARTUPPROC' AND OBJECTPROPERTY( OBJECT_ID( ROUTINE_NAME ), 'ExecIsStartup' ) = 1 )
    EXEC SP_PROCOPTION N'STARTUPPROC', 'STARTUP', 'OFF';
GO

-- Drop the stored procedures.
IF ( OBJECT_ID( N'dbo.STARTUPPROC' ) IS NOT Null )
    DROP PROCEDURE [dbo].[STARTUPPROC];
IF ( OBJECT_ID( N'dbo.CHECKDBS' ) IS NOT Null )
    DROP PROCEDURE [dbo].[CHECKDBS];
GO

-- Drop the temporary table.
IF ( OBJECT_ID( N'tempdb..##DBList' ) IS NOT Null )
    DROP TABLE ##DBList;
GO