Get the database given a sql task

Question

Get the database given a sql task

Ak-763166

Right there with Babe

Points: 772
More actions
February 5, 2009 at 9:29 pm

#134321

Hi All,
I need to get the database name involved for any given task via sql...
For example let's say for job sp_who2, this has to run against the 'master' database everytime.
Is my requirement feasible.?? If yes, can someone please help me out.
Thanks
A.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

I am not entirely sure on what you exactly wanted, but may be you are looking for DB_NAME(), this gives the database name in which it runs.

--Ramesh

Ak-763166 Right there with Babe Points: 772 More actions · Answer 2

Ok.

My requirement is as follows: for each sql jobs that is scheduled on the sql server, i need to know against which database the job is scheduled.

I have 100 sql task scheduled.It'll be difficult for me to open all the task and identify the database against which the job is scheduled.

Is there any sql whereby i can get this information. Can someone please help me out..

Thanks

A.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Here is the T-SQL to get the list of jobs with TSQL subsystem tasks...

SELECTj.name AS JobName, js.step_name AS StepName, js.database_name AS DatabaseName

FROMmsdb.dbo.sysjobs j

INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id

WHEREjs.subsystem = 'TSQL'

ORDER BY j.name

Note that, the above query doesn't check the contents of the command. So probably, there are chances that the T-SQL command in the step can change the context of the database...

--Ramesh

Ak-763166 Right there with Babe Points: 772 More actions · Answer 4

Hi Ramesh,

Thank you.

I am just curious here. Is there any sql statement which can provide me same info for DTS packages. That is the db name for a given package...

Thanks for your help and time.

A.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

amit (2/12/2009)
I am just curious here. Is there any sql statement which can provide me same info for DTS packages. That is the db name for a given package...

Unfortunately, I think this information is not available, because this information is stored in image data type.

--Ramesh