Check if someone use a database or not

USE tempdb
GO

DECLARE@dbVARCHAR(255),
@intervalINT,
@stopatDATETIME,
@totalusersINT,
@reportfileVARCHAR(500)

/*************************************************************************************
**PURPOSE:                                                                        **
**Check if a database is beeing used or not                                   **
**                                                                                  **
**COMPATIBLE WITH:                                                                **
**SQL 7.0                                                                     **
**SQL 8.0                                                                     **
**                                                                                  **
**VARIABLES:                                                                      **
**NAMEDESCRIPTION                                                 **
**======================================================================  **
**@dbWhat database to check                                      **
**cannot be NULL                                              **
**@intervalHow many milliseconds between each check                    **
**minimum value: 1                                            **
**maximum value: 999                                          **
**default 1 (each millisecond)                                **
**@stopatWhen the schudeled task will end                            **
**default NULL (never stop)                                   **
**FORMAT yyyy-mm-dd hh:mm:ss.mmm                              **
**@totalusersHow many users have to logon before reporting               **
**default 1                                                   **
**@reportfileFull local path and filename on the server for report       **
**default NULL (do not create reportfile).rpt"                **
**======================================================================  **
**                                                                                  **
**OBJECTS CREATED:                                                                **
**tempdb.dbo.database_<db>_usage_table                                        **
**tempdb.dbo.database_<db>_usage_proc                                         **
**A checkulded job named:                                                     **
**DATABASE USAGE CHECK - <mydb>                                           **
**@reportfile (if not NULL)                                                   **
**                                                                                  **
**WRITTEN BY:                                                                     **
**Robin Bäck (robin@robbac.com)                                               **
**                                                                                  **
**WRITTEN WHEN:                                                                   **
**2003-07-14                                                                  **
*************************************************************************************/
SELECT @db = 'mydb'
SELECT @interval = 10
SELECT @stopat = '2003-12-31'
SELECT @totalusers = 1
SELECT @reportfile = 'C:\MyReportFile.rpt'

SET NOCOUNT ON

DECLARE@jobnameVARCHAR(255),
@job_idUNIQUEIDENTIFIER,
@job_step_idUNIQUEIDENTIFIER,
@tempvarVARCHAR(255)

-- CHECK AND SET VARIABLES
IF ( @db IS NULL )
BEGIN
PRINT 'Variable @db has to be set'
GOTO the_end
END
IF NOT EXISTS ( SELECT * FROM master.dbo.sysdatabases WHERE name = @db )
BEGIN
PRINT 'DATABASE ' + @db + ' NOT FOUND ON THIS SERVER'
GOTO the_end
END
IF ( @interval IS NULL OR @interval < 1 OR @interval > 999 )
BEGIN
PRINT 'Variable @interval has to be an integer between 1 and 999'
GOTO the_end
END
IF ( @stopat < GETDATE() )
BEGIN
PRINT 'Variabel @stopat has to be after current time'
GOTO the_end
END
IF ( @stopat IS NULL )
SELECT @stopat = '9999-12-31'
IF ( @totalusers IS NULL )
SELECT @totalusers = 1


-- Create a temporary table in tempdb
IF EXISTS (
SELECT*
FROMdbo.sysobjects
WHEREname = 'database_' + @db + '_usage_table' AND
type = 'U' )
EXEC('DROP TABLE dbo.database_' + @db + '_usage_table')

EXEC ('
create table dbo.database_' + @db + '_usage_table (
checkdatesmalldatetimenull,
dbvarchar(50)null,
loginamevarchar(255)null,
nt_usernamenchar(128)null,
hostnamenchar(128)null
)
')

-- Create a proc to run in tempdb
IF EXISTS (
SELECT*
FROMdbo.sysobjects
WHEREname = 'database_' + @db + '_usage_proc' AND
type = 'P' )
EXEC('DROP PROCEDURE dbo.database_' + @db + '_usage_proc')

EXEC ('
CREATE PROC dbo.database_' + @db + '_usage_proc
@stopatDATETIME,
@intervalINT,
@totalusersINT
AS

SET NOCOUNT ON

IF ( @stopat < GETDATE() )
RAISERROR (''Variabel @stopat has to be after current time'', 16, 1)

DECLARE@waitfor DATETIME
SELECT@waitfor = GETDATE()

WHILE ( GETDATE() < @stopat )
BEGIN
-- Check if there are any users using the database
INSERT INTO dbo.database_' + @db + '_usage_table (
checkdate,
db,
loginame,
nt_username,
hostname
)
SELECTDISTINCT
GETDATE(),
DB_NAME(sp.dbid),
RTRIM(sp.loginame),
RTRIM(sp.nt_username),
RTRIM(sp.hostname)
FROMmaster.dbo.sysprocesses sp
LEFT OUTER JOIN dbo.database_' + @db + '_usage_table dut ON sp.loginame = dut.loginame
WHEREDB_NAME(sp.dbid) = ''' + @db + '''
AND dut.loginame IS NULL

-- Check if total users logged has been met
IF ( SELECT COUNT(*) FROM dbo.database_' + @db + '_usage_table ) >= @totalusers
GOTO out_of_loop

SELECT@waitfor = DATEADD(ms, @interval , GETDATE())

WAITFOR TIME @waitfor
END

out_of_loop:
SELECT*
FROMdbo.database_' + @db + '_usage_table
ORDER BY
checkdate
')

-- Create the sceduled job
SELECT@jobname = 'DATABASE USAGE CHECK - ' +  @db

IF EXISTS (
SELECT*
FROMmsdb.dbo.sysjobs
WHEREname = @jobname )
BEGIN
PRINT 'The scheduled job ''' + @jobname + ''' already exists'
GOTO the_end
END

-- Add the job
EXECmsdb.dbo.sp_add_job
@job_name =@jobname,
@description ='Job for checking database usage',
@start_step_id =1,
@owner_login_name ='sa',
@notify_level_eventlog =2, -- on failure
@job_id =@job_id OUTPUT

-- Add the jobstep
SELECT@tempvar =
'EXEC dbo.database_' + @db + '_usage_proc
@stopat = ''' + CONVERT(VARCHAR, @stopat, 21) + ''', -- yyyy-mm-dd hh:mm:ss.mmm
@interval = ' + CONVERT(VARCHAR, @interval) + ', -- min 0, max 999
@totalusers = ' + CONVERT(VARCHAR, @totalusers) + ' -- min 1, max 2^31-1'

EXECmsdb.dbo.sp_add_jobstep
@job_id =@job_id,
@step_id = 1,
@step_name =@jobname,
@subsystem ='TSQL',
@command =@tempvar,
@cmdexec_success_code =0,
@on_success_action =1, -- quit with success
@on_fail_action =2, -- quit with failure
@database_name ='tempdb'

-- Shall there be a reportfile
IF ( @reportfile IS NOT NULL )
EXEC msdb.dbo.sp_update_jobstep
@job_id =@job_id,
@step_id =1,
@step_name =@jobname,
@output_file_name =@reportfile

-- Set target server to local server
EXEC msdb.dbo.sp_add_jobserver
@job_id = @job_id,
@server_name = '(local)' 

-- Start the job
EXEC msdb.dbo.sp_start_job @job_id = @job_id

the_end:
GO