petesql

-- Generate SQL script to disable enabled SQL Server Agent jobs
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @sqlScript NVARCHAR(MAX) = ''

DECLARE job_cursor CURSOR FOR
    SELECT job_id
    FROM msdb.dbo.sysjobs
    WHERE enabled = 1; -- Only select jobs that are currently enabled

OPEN job_cursor

petesql

-- SQL Server User & Permissions Audit Script
USE master
GO

DECLARE
    @errorMessage nvarchar(4000),
    @errorNumber int,
    @errorSeverity int,
    @errorState int,
    @errorLine int,

petesql

-- Redshift list all schemas, tables and columns
select
	table_schema,
	table_name,
	ordinal_position as position,
	column_name,
	data_type,
	case when character_maximum_length is not null
		then character_maximum_length
		else numeric_precision end as max_length,

petesql

-- Find a string mssql
DECLARE @SQL VARCHAR(MAX) 
DECLARE @valueToFind VARCHAR(100) 
DECLARE @columnName VARCHAR(100) 

SET @valueToFind = '%pete%' 
SET @columnName = '%%' 

CREATE TABLE #TMP 
   (Clmn VARCHAR(500), 

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');

petesql

DECLARE @filename NVARCHAR(1000); 
DECLARE @bc INT; 
DECLARE @ec INT; 
DECLARE @bfn VARCHAR(1000); 
DECLARE @efn VARCHAR(10);   

-- Get the name of the current default trace 
SELECT 
  @filename = CAST(value AS NVARCHAR(1000)) 
FROM 

petesql

-- Show last backups on all databases
SELECT 
  ISNULL(d.[name], bs.[database_name]) AS [Database], d.recovery_model_desc AS [Recovery Model], 
    MAX(CASE WHEN [type] = 'D' THEN bs.backup_finish_date ELSE NULL END) AS [Last Full Backup],
    MAX(CASE WHEN [type] = 'I' THEN bs.backup_finish_date ELSE NULL END) AS [Last Differential Backup],
    MAX(CASE WHEN [type] = 'L' THEN bs.backup_finish_date ELSE NULL END) AS [Last Log Backup]
FROM 
  sys.databases AS d WITH (NOLOCK)
  LEFT OUTER JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
  ON bs.[database_name] = d.[name] 

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

-- Get full database backup history order by most recent
SELECT 
    bs.server_name, 
    bs.database_name,
    bs.user_name,
	bms.software_name AS backup_software,
	bs.recovery_model,
	bs.type,
	backup_type = CASE
			WHEN bs.type = 'D'

petesql

-- Get estimated backup & restore time
SELECT
	r.session_id,
	r.user_id,
	r.start_time,
	dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time,
	r.percent_complete,
	r.command,
	d.name,
	s.text AS full_command_text