SELECT DISTINCT T1.server AS [Server Name],

T1.step_id AS [Step_id],
T1.step_name AS [Step Name],
SUBSTRING(T2.name,1,140) AS [SQL Job Name],
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
CAST(CONVERT(DATETIME,CAST(run_date AS CHAR(8)),101) AS CHAR(11)) AS [Failure Date],
msdb.dbo.agent_datetime(T1.run_date, T1.run_time) AS 'RunDateTime',
T1.run_duration StepDuration,
CASE T1.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress'
END AS ExecutionStatus,
T1.message AS [Error Message]
FROM
msdb..sysjobhistory T1 INNER JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
WHERE
T1.run_status NOT IN (1, 4)
AND T1.step_id != 0
AND run_date >= CONVERT(CHAR(8), (SELECT DATEADD (DAY,(-1), GETDATE())), 112)

This script disabled all nonclusterd indexes. This can be useful for ETL processes.

-- disable all nonclustered indices
SET DEADLOCK_PRIORITY HIGH

DECLARE @DisableIndexen NVARCHAR(max);

DECLARE cur_rebuild CURSOR FOR 
   SELECT 'ALTER INDEX ' +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(t.name) + ' DISABLE' 
   FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id 
   WHERE I.type_desc = 'NONCLUSTERED' and i.is_disabled = 0 ORDER BY t.name, i.name;
OPEN cur_rebuild;
FETCH NEXT FROM cur_rebuild INTO @DisableIndexen;
WHILE @@FETCH_STATUS = 0
   BEGIN
 --  print @DisableIndexen
     EXECUTE sp_executesql  @DisableIndexen;
      FETCH NEXT FROM cur_rebuild INTO @DisableIndexen;
   END;
CLOSE cur_rebuild;
DEALLOCATE cur_rebuild;

With this script you can enable the disabled indexes

-- enable all nonclustered indices
SET DEADLOCK_PRIORITY HIGH

DECLARE @RebuildIndexen NVARCHAR(200);

DECLARE cur_rebuild CURSOR FOR 
   SELECT 'ALTER INDEX ' +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(t.name) + ' REBUILD' 
   FROM sys.indexes i JOIN sys.tables t ON i.object_id = t.object_id 
   WHERE i.is_disabled = 1 ORDER BY t.name, i.name;
OPEN cur_rebuild;
FETCH NEXT FROM cur_rebuild INTO @RebuildIndexen;
WHILE @@FETCH_STATUS = 0
   BEGIN
      EXECUTE sp_executesql  @RebuildIndexen;
      FETCH NEXT FROM cur_rebuild INTO @RebuildIndexen;
   END;
CLOSE cur_rebuild;
DEALLOCATE cur_rebuild;

List all CLR functions for each database

create table ##assembly_modules
(
   DbName sysname
  ,object_id int
  ,assembly_id int
  ,assembly_class sysname
  ,assembly_method sysname
  ,null_on_null_input int
  ,execute_as_principal_id int
)
exec sp_msforeachdb 'insert into ##assembly_modules select ''?'' as DbName, * from [?].sys.assembly_modules'


select * from ##assembly_modules

drop table ##assembly_modules

Today I needed to replace some SSIS configuration values in the database.  I used this script to generate the update statements. The update statements replaces the old sql provider 2008 to 2012 and relaces a old instance name to a new one.

declare @SqlInstance_old nvarchar(100) = 'INSTANCE1'
declare @SqlInstance_new nvarchar(100) = 'INSTANCE2'

declare @SqlProvider_old nvarchar(50) = 'SQLNCLI.1'
declare @SqlProvider_new nvarchar(50) = 'SQLNCLI11.1'

-- Generate update script
select 
QUOTENAME(TABLE_NAME) as table_name,
'update tbl 
set ConfiguredValue = REPLACE(REPLACE(ConfiguredValue,'''+@SqlInstance_old+''','''+@SqlInstance_new+'''),'''+@SqlProvider_old+''', '''+@SqlProvider_new+''') 
FROM '+QUOTENAME(table_catalog)+'.'+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' as tbl 
WHERE ConfiguredValue like ''Data Source=%'' and ConfiguredValue like ''%'+@SqlInstance_old+'%'''+char(10)+'GO'+char(10)
from information_schema.columns as c 
where column_name = 'ConfiguredValue'

-- Generate check script
select 
  'SELECT *
   FROM '+QUOTENAME(table_catalog)+'.'+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' 
   WHERE ConfiguredValue like ''Data Source=%'' and ConfiguredValue not like ''%'+@SqlInstance_new+'%'''
from information_schema.columns as c 
where column_name = 'ConfiguredValue'