Otterpohl

SELECT T.DatabaseName,
       T.ObjectId,
       T.ObjectName,
       T.IndexId,
       T.IndexDescription,
       CONVERT(DECIMAL(16, 1), (SUM(T.avg_record_size_in_bytes * T.record_count) / (1024.0 * 1024))) AS [IndexSize(MB)],
       T.lastupdated AS [StatisticLastUpdated],
       T.AvgFragmentationInPercent
FROM
(

Otterpohl

WITH RedundantQueries
AS (SELECT TOP 10
           query_hash,
           COUNT(query_hash) AS PlansCached,
           COUNT(DISTINCT (query_hash)) AS DistinctPlansCached,
           MIN(creation_time) AS FirstPlanCreationTime,
           MAX(creation_time) AS LastPlanCreationTime,
           SUM(total_worker_time) AS Total_CPU_ms,
           SUM(total_elapsed_time) AS Total_Duration_ms,
           SUM(total_logical_reads) AS Total_Reads,

Otterpohl

SELECT c.Name,
       c.[Path],
       COUNT(*) AS TimesRun,
       MAX(l.TimeStart) AS [LastRun],
(
    SELECT SUBSTRING(
                    (
                        SELECT CAST(', ' AS VARCHAR(MAX))+CAST(c1.Name AS VARCHAR(MAX))
                        FROM [ReportServer].[dbo].[Catalog] AS c
                        INNER JOIN [ReportServer].[dbo].[DataSource] AS d ON c.ItemID = d.ItemID

Otterpohl

DECLARE @SchemaName VARCHAR(100);
DECLARE @TableName VARCHAR(256);
DECLARE @IndexName VARCHAR(256);
DECLARE @ColumnName VARCHAR(100);
DECLARE @is_unique VARCHAR(100);
DECLARE @IndexTypeDesc VARCHAR(100);
DECLARE @FileGroupName VARCHAR(100);
DECLARE @is_disabled VARCHAR(100);
DECLARE @IndexOptions VARCHAR(MAX);
DECLARE @IndexColumnId INT;

Otterpohl

CREATE FUNCTION [dbo].[fn_StripHTML] (@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @Start INT = CHARINDEX('<',@HTMLText)
    DECLARE @End INT = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
    DECLARE @Length INT = (@End - @Start) + 1

    WHILE @Start > 0 AND @End > 0 AND @Length > 0
    BEGIN
        SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')

Otterpohl

SET NOCOUNT ON
DECLARE  @SchemaName NVARCHAR(100) = 'mipl'

SELECT 'DROP ' +
                CASE WHEN type IN ('P','PC') THEN 'PROCEDURE'
                        WHEN type =  'U' THEN 'TABLE'
                        WHEN type IN ('IF','TF','FN') THEN 'FUNCTION'
                        WHEN type = 'V' THEN 'VIEW'
                    END +
                ' ' +  QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)   as DropStatement

Otterpohl

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' + CAST(j.job_id AS VARCHAR(150)) + ''', @owner_login_name=N''sa'' '
FROM msdb.dbo.sysjobs AS j
    INNER JOIN master.sys.syslogins AS L
        ON j.owner_sid = L.sid
    INNER JOIN msdb.dbo.syscategories AS C
        ON C.category_id = j.category_id
WHERE msdb.dbo.SQLAGENT_SUSER_SNAME(j.owner_sid) <> 'sa';

Otterpohl

SELECT  'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_SHRINK OFF WITH NO_WAIT;' AS [To Execute]
FROM    sys.databases
WHERE   is_auto_shrink_on = 1;

Otterpohl

SELECT 'ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(d.[name]) + ' to sa'
FROM [sys].[databases] d
INNER JOIN [sys].[server_principals] s ON s.sid = d.owner_sid
WHERE d.[name] NOT IN ('master','msdb','model','tempdb')
	AND s.[name] <> 'sa'

Otterpohl

EXEC sys.sp_configure N'show advanced options', 1 
GO
RECONFIGURE WITH OVERRIDE
GO

DECLARE @DOP INT

SELECT @DOP = CASE 
				WHEN cpu_count > 16 THEN 8
				else cpu_count / 2