SQL Server script to create missing indexes on all foreign keys

Here is a handy script to create missing indexes on all foreign keys.
Based on the script over at lukelowrey.com.

-- Call procedure to retrieve error information.  
CREATE or ALTER proc [Utility].[usp_GetErrorMessage](
	@message nvarchar(max) output,
	@procid int = null
)
as
set @message = 
	case
		when @procid is not null then N'Proc=' + object_schema_name(@procid) + N'.' + object_name(@procid) + N', '
		else N'<unknown procedure>'
	end +
	N'ErrorNumber=' + convert(nvarchar(max),error_number()) +
	N', Severity=' + convert(nvarchar(max),error_severity()) +
	N', State=' + convert(nvarchar(max),error_state()) +
	N', Line=' + convert(nvarchar(max),error_line()) +
	N', Message=' + convert(nvarchar(max),error_message());
GO

CREATE or ALTER proc [Utility].[usp_AutomaticallyCreateIndexesForForeignKeys](
	@Execute bit = null
)
as
begin;
	set nocount on;

	declare @CurrentSQLCommand nvarchar(max);
	declare @IndexName TABLE ( [SQL] nvarchar(450) primary key );
	declare @message nvarchar(max);

	WITH v_NonIndexedFKColumns AS (
		SELECT
			[Table_Name] = OBJECT_NAME(a.[parent_object_id]),
			[Column_Name] = b.[name]
		FROM sys.foreign_key_columns [a]
			,sys.all_columns [b]
			,sys.objects [c]
		WHERE a.[parent_column_id] = b.[column_id]
			AND a.[parent_object_id] = b.[object_id]
			AND b.[object_id] = c.[object_id]
			AND c.[is_ms_shipped] = 0
		EXCEPT
			SELECT
				[Table_Name] = OBJECT_NAME(a.[object_id]),
				[Column_Name] = b.[name]
			FROM sys.index_columns [a]
				,sys.all_columns [b]
				,sys.objects [c]
			WHERE a.[object_id] = b.[object_id]
				AND a.[key_ordinal] = 1
				AND a.[column_id] = b.[column_id]
				AND a.[object_id] = c.[object_id]
				AND c.[is_ms_shipped] = 0
	)
	INSERT INTO @IndexName( [SQL] )
	SELECT
		'CREATE INDEX [ix_' + v.[Table_Name] + '_' + v.[Column_Name] + ']'
		+ ' ON [' + SCHEMA_NAME(fk.[schema_id]) + '].[' + v.[Table_Name] + '] ([' + v.[Column_Name] + ']);'
	FROM v_NonIndexedFKColumns [v]
		,sys.all_columns [c]
		,sys.all_columns [c2]
		,sys.foreign_key_columns [fkc]
		,sys.foreign_keys [fk]
	WHERE v.[Table_Name] = OBJECT_NAME(fkc.[parent_object_id])
		AND v.[Column_Name] = c.[name]
		AND fkc.[parent_column_id] = c.[column_id]
		AND fkc.[parent_object_id] = c.[object_id]
		AND fkc.[referenced_column_id] = c2.[column_id]
		AND fkc.[referenced_object_id] = c2.[object_id]
		AND fk.[object_id] = fkc.[constraint_object_id];

	DECLARE sqlCommands CURSOR FOR SELECT [SQL]
	FROM @IndexName;

	OPEN sqlCommands;
	FETCH NEXT FROM sqlCommands INTO @CurrentSQLCommand;

	while @@FETCH_STATUS = 0 begin;
		print @CurrentSQLCommand;

		if @Execute = 1 begin try;
			exec sp_executesql @CurrentSQLCommand;
		end try
		begin catch;
			exec [Utility].[usp_GetErrorMessage] @[email protected] output, @[email protected]@PROCID;
			throw 51000, @message, 1;
		end catch;

		fetch next from sqlCommands into @CurrentSQLCommand;
	end;
	close sqlCommands;
	deallocate sqlCommands;
end;

PowerShell script to shrink & rebuild your SQL Server databases.

Just change the DB1, DB2, DB3, etc.. to be the actual databases you plan on rebuilding.
Same with the ServerName\InstanceName.

Caveat: Don’t run this script on a regular basis. If your databases keep growing in size, there’s usually a reason for it.

$databases = @('DB1','DB2','DB3')
$watch = New-Object System.Diagnostics.Stopwatch
foreach ($database in $databases) {
 $watch.Start()
 Write-Host "Shrinking - " $database
 Invoke-DbaDbShrink -SqlInstance "ServerName\InstanceName" -Database $database -PercentFreeSpace 10 -FileType Data -StepSize 1GB
 Write-Host $time " done " $database
 Write-Host "Rebuilding " $database
 Invoke-DbaQuery -SqlInstance "ServerName\InstanceName" -Database $database -Query "Exec sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'"
 $time = $watch.Elapsed.TotalMinutes
 $watch.reset()
 Write-Host $time " - " $database
}

Enable Windows Firewall for SQL Server

Run these commands on the Windows Server to open the ports for listening on a named instance of SQL Server.
Note: Remember to enable TCP in the SQL Server Configuration Manager.

netsh firewall set portopening protocol=TCP port=1433 name=SQLServerTCP mode=ENABLE scope=SUBNET profile=CURRENT

netsh firewall set portopening protocol=UDP port=1434 name=SQLServerUDP mode=ENABLE scope=SUBNET profile=CURRENT

For more help, refer to SQL Server & Windows Firewall.