Procedures / Commands used in Sql Server Jobs

It is very difficult to find a procedures / Commands used in SQL Server Jobs from the huge cluster of Jobs through SQL Server Management Studio. You can get a list of all the Procedures / commands that are used in Sql Server Jobs.


USE msdb
GO
SELECT sj.name AS job_name, st.command
FROM sysjobs sj
JOIN sysjobsteps st
ON sj.job_id = st.job_id

You can use a where clause to get to know about procedures / command used in a particular jobs:

where st.command like '%Proc_toFind%'  

Changing the Default Database

By default SQL Server loads master databse.
Sometimes it is required to change the default database in SQL Server. Use below query to chage the default database:

ALTER LOGIN [ArunL\Arun] with default_database = DemoData

Find database file size

Use below query to get the database file size. I have used system view “sysaltfiles” from “master” database which keeps information about each and every database file available in current SQL Server instance. 


SELECT DB_NAME(dbid) AS DatabaseName, 
Name AS LogicalFileName, 
CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType, 
FileName AS FilePath, size AS TotalPage, 
(Size*8192E)/1048576 AS FileSizeInMB
FROM master..SysAltFiles