Tag: foreign keys

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