LitKnd

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: [email protected]
Copyright (c) 2018 SQL Workbooks LLC
Permission is hereby granted, free of charge, to any person 
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without 
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom 

petesql

-- list all sql agent jobs on instance
SELECT
	j.job_id,
	j.name, j.description,
	j.enabled,
	s.step_id, s.step_name, s.database_name, s.subsystem, s.command,
	j.date_created, j.date_modified,
	s.on_success_action, s.on_fail_action, s.retry_attempts, s.retry_interval, 
	s.last_run_outcome, s.last_run_duration, s.last_run_date, s.last_run_time
FROM msdb.dbo.sysjobs j WITH (NOLOCK)

petesql

-- get database last restore time
SELECT
    [rs].[destination_database_name] AS [DatabaseName],
    [rs].[restore_date] AS [LastRestoreDate]
FROM
    msdb..restorehistory rs
INNER JOIN
    (SELECT
        [destination_database_name],
        MAX([restore_date]) AS [last_restore_date]

petesql

-- Get sizes of all databases and store into a temp table.
SELECT 
    DB_NAME(database_id) AS [database_name],
	CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) AS [data_size_mb],
	--CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 / 1024 AS DECIMAL(8,2)) AS [data_size_gb],
    CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) AS [log_size_mb],
	--CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 / 1024 AS DECIMAL(8,2)) AS [log_size_gb],
    CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) AS [total_size_mb]
	--,CAST(SUM(size) * 8. / 1024 / 1024 AS DECIMAL(8,2)) AS [total_size_gb]
FROM sys.master_files WITH(NOWAIT)

petesql

-- generate script, alter database move temp db
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
	+ ' FILENAME = ''D:\mssql_temp_db\' + f.name
	+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END + ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');