Querying SQL Server Online Database Information

/*Fetch SQL Server Online Database Details

Created By: Adarsh Agrawal

Created Date: 04/30/2015

*/

DECLARE @Name VARCHAR(MAX) = ''

DECLARE @Table TABLE(Name VARCHAR(500))

INSERT INTO @Table

Select Name as [Online Database] FROM sys.databases (Nolock)

Where state_desc = 'ONLINE'

Order by name asc;

Select @Name = @Name + [Name] + ', '

FROM  @Table

SELECT @@SERVERNAME as [Server Name],

CASE WHEN LEN(@Name) = 0 THEN ''

ELSE SUBSTRING(@Name,1,LEN(@Name)-1) END [Online Database]

Querying SQL Server Authentication Mode

/*Fetch SQL Server Authentication Mode

Created By: Adarsh Agrawal

Created Date: 04/29/2015

*/

DECLARE @AuthenticationMode INT 

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @AuthenticationMode OUTPUT 

SELECT CASE @AuthenticationMode   

            WHEN 1 THEN 'Windows Authentication'  

            WHEN 2 THEN 'Windows and SQL Server Authentication'  

      ELSE 'Unknown' 

END as [Authentication Mode]

Querying SQL Server Agent Job Information

/*Fetch SQL Server Agent Job Details

Created By: Adarsh Agrawal

Created Date: 04/06/2015

*/

CREATE TABLE #JobResults (Job_ID UNIQUEIDENTIFIER NOT NULL,

Last_Run_Date INT NOT NULL,

Last_Run_Time INT NOT NULL,

Next_Run_date INT NOT NULL,

Next_Run_Time INT NOT NULL,

Next_Run_Schedule_ID INT NOT NULL,

Requested_to_Run INT NOT NULL,

Request_Source INT NOT NULL,

Request_Source_id SYSNAME

COLLATE Database_Default NULL,

Running INT NOT NULL,

Current_Step INT NOT NULL,

Current_Retry_Attempt INT NOT NULL,

Job_State INT NOT NULL )

INSERT  #JobResults

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, '';

SELECT job.name AS [JobName],

(SELECT MAX(CAST( STUFF(STUFF(CAST(jh.run_date AS VARCHAR),7,0,'-'),5,0,'-') + ' ' +

STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),' ','0'),5,0,':'),3,0,':') AS DATETIME))

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobhistory AS jh

ON jh.job_id = j.job_id AND jh.step_id = 0 ) AS [LastCompletedDateTime],

(SELECT  TOP 1 start_execution_date

FROM msdb.dbo.sysjobactivity

WHERE job_id = r.job_id

ORDER BY start_execution_date DESC) AS [JobStartDateTime],

CASE

            WHEN r.running = 0

THEN

CASE

            WHEN jobInfo.lASt_run_outcome = 0 THEN 'Failed'

            WHEN jobInfo.lASt_run_outcome = 1 THEN 'Success'

            WHEN jobInfo.lASt_run_outcome = 3 THEN 'Canceled'

            ELSE 'Unknown'

END

            WHEN r.job_state = 0 THEN 'Success'

            WHEN r.job_state = 4 THEN 'Success'

            WHEN r.job_state = 5 THEN 'Success'

            WHEN r.job_state = 1 THEN 'In Progress'

            WHEN r.job_state = 2 THEN 'In Progress'

            WHEN r.job_state = 3 THEN 'In Progress'

            WHEN r.job_state = 7 THEN 'In Progress'

            ELSE 'Unknown'

END AS [RunStatusDescription]

FROM #JobResults AS r

LEFT OUTER JOIN msdb.dbo.sysjobservers AS jobInfo

ON r.job_id = jobInfo.job_id

INNER JOIN msdb.dbo.sysjobs AS job

ON r.job_id = job.job_id

WHERE job.[enabled] = 1

Order by JobName

Drop table #JobResults