Using current database name in T-SQL has Using statement

Question

Hello everybody. I have application runs T-SQL statements to update more than one database the problem is i'm using the following t-sql

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'test2', 
        @enabled=1, 
        @start_step_id=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=N'', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', 
        @notify_email_operator_name=N'', 
        @notify_netsend_operator_name=N'', 
        @notify_page_operator_name=N'', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'test2', @server_name = N'AMR-PC\SQL2008'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'test2', @step_name=N'test', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_fail_action=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC sp_MSforeachdb ''
    EXEC sp_MSforeachtable @command1=''''DBCC DBREINDEX (''''''''*'''''''')'''', 
        @replacechar=''''*''''''', 
        @database_name=N'Client5281', 
        @output_file_name=N'C:\Documents and Settings\Amr\Desktop\Scheduel Reports\report', 
        @flags=2
GO

USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'test2', @name=N'test', 
        @enabled=1, 
        @freq_type=8, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20100517, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

and i'm using (USE [msdb]) before any block and i want to get database name to replace this @database_name=N'**Client5281**', with the current database name instead of ([msdb]) that i'm using.

i hope that i explained what i want well.

Answer 1

The only way to do that is dynamic SQL. For example:

declare @sql nvarchar(max)
set @sql = N'use ' + @database_name + '
    go
    declare @schedule_id int
    ...'
exec (@sql)

In response to your comment, you can save the current database name like:

declare @db varchar(256)
set @db = DB_NAME()

And then change:

@database_name=N'Client5281', 

to:

@db,

You will also have to remove any go statements, as those limit the variable scope.

Answer 2

Remove all use [msdb], they are not needed. Since the script fully qualifies the procedure name with the db name and schema name, there is no need to USE before invoking the procedure. You can then simply use @database_name=DB_NAME ().

Another solution is to use SQLCMD variables, which SSMS recognizes when properly configured:

:setvar dbname Client5281
...
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'test2', @step_name=N'test', 
        ...
        @database_name=N'$(dbname)', 
        ...
GO

Answer 3

beacause i execute this query from vb.net application i used replace() method to replace the dbname by current dbname. thanks for all tried to help me.