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
}

List collation on all columns in all tables in database on SQL Server

select
    concat( quotename( [s].[name] ), N'.', quotename( [t].[name] ), N'.', quotename( [c].[name] ) ) [Column Name]
   ,[c].[collation_name] [Collation]
from [sys].[schemas] [s]
join [sys].[tables] [t] on [t].[schema_id] = [s].[schema_id]
join [sys].[columns] [c] on [c].[object_id] = [t].[object_id]
where [c].[collation_name] is not null
order by [Column Name];

Example:

How to create unsigned data types in SQL Server

Script to create unsigned data types in SQL Server.

USE [Numbers]
GO

DROP TYPE if exists [dbo].[UInt16];
DROP TYPE if exists [dbo].[UInt32];
DROP TYPE if exists [dbo].[UInt64];
DROP TYPE if exists [dbo].[UFloat];
DROP TYPE if exists [dbo].[UReal];
DROP TYPE if exists [dbo].[ZeroToOne];
GO

DROP RULE if exists [dbo].[UInt16];
DROP RULE if exists [dbo].[UInt32];
DROP RULE if exists [dbo].[UInt64];
DROP RULE if exists [dbo].[UFloat];
DROP RULE if exists [dbo].[UReal];
DROP RULE if exists [dbo].[ZeroToOne];
GO

CREATE TYPE dbo.[UInt16] FROM smallint NULL;
CREATE TYPE dbo.[UInt32] FROM int NULL;
CREATE TYPE dbo.[UInt64] FROM bigint NULL;
CREATE TYPE dbo.[UFloat] FROM float NULL;
CREATE TYPE dbo.[UReal] FROM real NULL;
CREATE TYPE dbo.[ZeroToOne] FROM float NOT NULL;
GO

CREATE RULE dbo.UInt16 AS @value >= 0;
GO
CREATE RULE dbo.UInt32 AS @value >= 0;
GO
CREATE RULE dbo.UInt64 AS @value >= 0;
GO
CREATE RULE dbo.UFloat AS @value >= 0;
GO
CREATE RULE dbo.UReal AS @value >= 0;
GO
CREATE RULE dbo.ZeroToOne AS @value between 0.0 and 1.0;
GO

List Cached Data Per Object in Memory

select
	(count(*) * 8) / 1024 as [MB Used],
	object_schema_name( obj.[object_id] ) [Schema Name],
    obj.[name] [tablename],
	obj.[index_id]
from sys.dm_os_buffer_descriptors bd
join (
	select
		object_name( p.[object_id] ) [name],
		p.[index_id],
		au.[allocation_unit_id],
		p.[object_id]
	from sys.allocation_units au
	join sys.partitions p on ( au.[container_id] = p.[hobt_id] and ( au.[type] = 1 or au.[type] = 3 ) )
		or ( au.[container_id] = p.[partition_id] and au.[type] = 2 )
    ) obj on bd.[allocation_unit_id] = obj.[allocation_unit_id]
where bd.[database_id] = db_id()
group by object_schema_name( obj.[object_id] ), [name], [index_id]
order by [MB Used] desc

SQL Server Function: How to format bytes into greater units like MB, GB, TB, etc

Here’s a link to my gist for a SQL script to return a string from bytes formatted as a larger unit.

Examples

select [dbo].[FormatBytes]( 1324.13, 'byte' );
select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );

Link: https://gist.github.com/Protiguous/c40ef32ee4fa0f4e93c9413d6cc3d6bd

select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
select [dbo].[FormatBytes]( 132413225.567567, 'bytes' );
select [dbo].[FormatBytes]( 1324132255.567567, 'bytes' );
select [dbo].[FormatBytes]( 13241322551.567567, 'bytes' );
select [dbo].[FormatBytes]( 13241322.567567, 'mb' );
select [dbo].[FormatBytes]( 132413225.567567, 'gb' );
select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );
select [dbo].[FormatBytes]( 13241322551.567567, 'zb' );
select [dbo].[FormatBytes]( 13241322551.567567, 'yb' );
select [dbo].[FormatBytes]( 132413225512.567567, 'yb' );
select [dbo].[FormatBytes]( 132413225, 'bb' );
select [dbo].[FormatBytes]( 1324132253, 'bb' ); –too big!
select [dbo].[FormatBytes]( 1324.13, 'byte' );
select [dbo].[FormatBytes]( 1324135, 'geopbyte' ); –too big!
create or alter function [dbo].[FormatBytes] ( @bytes decimal(38, 0), @toUnit nvarchar(15) = N'bytes' )
returns sysname
with schemabinding
as
begin
declare @prefix decimal(38, 0); adjust precision to your needs. Can scale higher bytes the lower precision is.
set @toUnit = trim(@toUnit);
if @toUnit is null return null;
set @bytes = @bytes *
case @toUnit collate SQL_Latin1_General_CP1_CI_AI
when N'b' then 1
when N'byte' then 1
when N'bytes' then 1
when N'kb' then 1024
when N'kilobyte' then 1024
when N'kilobytes' then 1024
when N'mb' then 1048576
when N'megabyte' then 1048576
when N'megabytes' then 1048576
when N'gb' then 1073741824
when N'gigabyte' then 1073741824
when N'gigabytes' then 1073741824
when N'tb' then 1099511627776
when N'terabyte' then 1099511627776
when N'terabytes' then 1099511627776
when N'pb' then 1125899906842624
when N'petabyte'then 1125899906842624
when N'petabytes' then 1125899906842624
when N'eb' then 1152921504606846976
when N'exabyte' then 1152921504606846976
when N'exabytes' then 1152921504606846976
when N'zb' then 1180591620717411303424
when N'zettabyte'then 1180591620717411303424
when N'zettabytes' then 1180591620717411303424
when N'yb' then 1208925819614629174706176
when N'yottabyte' then 1208925819614629174706176
when N'yottabytes' then 1208925819614629174706176
when N'bb' then 1237940039285380274899124224
when N'brontobyte' then 1237940039285380274899124224
when N'brontobytes' then 1237940039285380274899124224
when N'geopbyte' then 1267650600228229401496703205376
when N'geopbytes' then 1267650600228229401496703205376
end;
set @prefix =
case
when abs(@bytes) < 1024 then @bytes bytes
when abs(@bytes) < 1048576 then (@bytes / 1024) kb
when abs(@bytes) < 1073741824 then (@bytes / 1048576) mb
when abs(@bytes) < 1099511627776 then (@bytes / 1073741824) gb
when abs(@bytes) < 1125899906842624 then (@bytes / 1099511627776) tb
when abs(@bytes) < 1152921504606846976 then (@bytes / 1125899906842624) pb
when abs(@bytes) < 1180591620717411303424 then (@bytes / 1152921504606846976) eb
when abs(@bytes) < 1208925819614629174706176 then (@bytes / 1180591620717411303424) zb
when abs(@bytes) < 1237940039285380274899124224 then (@bytes / 1208925819614629174706176) yb
when abs(@bytes) < 1267650600228229401496703205376 then (@bytes / 1237940039285380274899124224) bb
else (@bytes / 1267650600228229401496703205376) geopbytes
end;
return convert(sysname, @prefix) +
case
when abs(@bytes) < 1024 then N' Bytes'
when abs(@bytes) < 1048576 then N' KB'
when abs(@bytes) < 1073741824 then N' MB'
when abs(@bytes) < 1099511627776 then N' GB'
when abs(@bytes) < 1125899906842624 then N' TB'
when abs(@bytes) < 1152921504606846976 then N' PB'
when abs(@bytes) < 1180591620717411303424 then N' EB'
when abs(@bytes) < 1208925819614629174706176 then N' ZB'
when abs(@bytes) < 1237940039285380274899124224 then N' YB'
when abs(@bytes) < 1267650600228229401496703205376 then N' BB'
else N' geopbytes'
end;
end;
view raw FormatBytes.sql hosted with ❤ by GitHub

Don’t let this little SQL slip delete your rows!

I came across this quick example that shows how easily the tiniest little mistake could end up deleting rows from your table.

The solution to this bug? Always name & reference your tables, CTE, and subqueries!

-- Create and populate test tables.
drop table if exists dbo.purge_test;

create table dbo.purge_test(
    id int primary key,
    purge_type int not null default 1, 
    purge_date datetime not null default current_timestamp
);

insert into dbo.purge_test(id) values(99);

drop table if exists dbo.star_wars_film;

create table dbo.star_wars_film (
    film_id int primary key,
    film_description varchar(50) not null
);

insert into dbo.star_wars_film(film_id, film_description)
values 
    (1,'Star Wars - The phantom menace'),
     (2,'Revenge of the Sith'),
     (3,'Attack of the clones'),
     (4,'A new hope'),
     (5,'The empire strikes back'),
     (6,'Return of the Jedi'),
     (7,'The force awakens'),
     (8,'The last Jedi'),
     (-1,'Rogue One');

And now you can play: Spot the Bug™!

-- Check the data before the unspotted bug.
select film_id, film_description from dbo.star_wars_film order by film_id;

-- Bug! Do you see it? The query runs just fine.. right?
delete from dbo.star_wars_film
where film_id in (
    select film_id from dbo.purge_test where purge_type = 1
);

-- After the delete.
-- Expected the same rows as before because we *know* the purge id of 99 wasn't one of the file_id.
-- Right?
select film_id, film_description from dbo.star_wars_film order by film_id;

-- Corrected query. Won't execute now because specified column doesn't exist.
delete swf
from dbo.star_wars_film swf
where swf.film_id in (
    select pt.film_id from dbo.purge_test pt where pt.purge_type = 1
);

SQL Function to convert bytes to string

-- select [dbo].[FormatBytes]( 13241322.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 132413225.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 1324132255.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 13241322551.567567, 'bytes' );
-- select [dbo].[FormatBytes]( 13241322.567567, 'mb' );
-- select [dbo].[FormatBytes]( 132413225.567567, 'gb' );
-- select [dbo].[FormatBytes]( 1324132255.567567, 'tb' );
-- select [dbo].[FormatBytes]( 13241322551.567567, 'zb' );
-- select [dbo].[FormatBytes]( 13241322551.567567, 'yb' );
-- select [dbo].[FormatBytes]( 132413225512.567567, 'yb' );
-- select [dbo].[FormatBytes]( 132413225, 'bb' );
-- select [dbo].[FormatBytes]( 1324132253, 'bb' );	--too big!
-- select [dbo].[FormatBytes]( 132413, 'geopbyte' );
-- select [dbo].[FormatBytes]( 1324135, 'geopbyte' );	--too big!

create or alter function [dbo].[FormatBytes] ( @bytes decimal(38, 2), @toUnit nvarchar(15) = N'bytes' )
returns sysname
with schemabinding
as
begin
	set @bytes = @bytes *
		case @toUnit collate SQL_Latin1_General_CP1_CI_AI

			when N'b' then 1
			when N'byte' then 1
			when N'bytes' then 1

			when N'kb' then 1024
			when N'kilobyte' then 1024
			when N'kilobytes' then 1024

			when N'mb' then 1048576
			when N'megabyte' then 1048576
			when N'megabytes' then 1048576

			when N'gb' then 1073741824
			when N'gigabyte' then 1073741824
			when N'gigabytes' then 1073741824
			
			when N'tb' then 1099511627776
			when N'terabyte' then 1099511627776
			when N'terabytes' then 1099511627776

			when N'pb' then 1125899906842624
			when N'petabyte'then 1125899906842624
			when N'petabytes' then 1125899906842624

			when N'eb' then 1152921504606846976
			when N'exabyte' then 1152921504606846976
			when N'exabytes' then 1152921504606846976

			when N'zb' then 1180591620717411303424
			when N'zettabyte'then 1180591620717411303424
			when N'zettabytes' then 1180591620717411303424

			when N'yb' then 1208925819614629174706176
			when N'yottabyte' then 1208925819614629174706176
			when N'yottabytes' then 1208925819614629174706176

			when N'bb' then 1237940039285380274899124224
			when N'brontobyte' then 1237940039285380274899124224
			when N'brontobytes' then 1237940039285380274899124224

			when N'geopbyte' then 1267650600228229401496703205376
			when N'geopbytes' then 1267650600228229401496703205376
		end;
			
	declare @prefix decimal(38, 0) = --adjust precision to your needs. Can scale higher bytes the lower precision is
		case 
			when abs(@bytes) < 1024 then @bytes --bytes 
			when abs(@bytes) < 1048576 then (@bytes / 1024) --kb 
			when abs(@bytes) < 1073741824 then (@bytes / 1048576) --mb 
			when abs(@bytes) < 1099511627776 then (@bytes / 1073741824) --gb 
			when abs(@bytes) < 1125899906842624 then (@bytes / 1099511627776) --tb 
			when abs(@bytes) < 1152921504606846976 then (@bytes / 1125899906842624) --pb 
			when abs(@bytes) < 1180591620717411303424 then (@bytes / 1152921504606846976) --eb 
			when abs(@bytes) < 1208925819614629174706176 then (@bytes / 1180591620717411303424) --zb 
			when abs(@bytes) < 1237940039285380274899124224 then (@bytes / 1208925819614629174706176) --yb 
			when abs(@bytes) < 1267650600228229401496703205376 then (@bytes / 1237940039285380274899124224) --bb 
			else (@bytes / 1267650600228229401496703205376) --geopbytes 
		end;

	return convert(sysname, @prefix) +
		case
				when abs(@bytes) < 1024 then N' Bytes'
				when abs(@bytes) < 1048576 then N' KB'
				when abs(@bytes) < 1073741824 then N' MB'
				when abs(@bytes) < 1099511627776 then N' GB'
				when abs(@bytes) < 1125899906842624 then N' TB'
				when abs(@bytes) < 1152921504606846976 then N' PB'
				when abs(@bytes) < 1180591620717411303424 then N' EB'
				when abs(@bytes) < 1208925819614629174706176 then N' ZB'
				when abs(@bytes) < 1237940039285380274899124224 then N' YB'
				when abs(@bytes) < 1267650600228229401496703205376 then N' BB'
				else N' geopbytes'
			end;
end;