Skip to content

Finding non-explicit relationships in SQL

August 13, 2011

Sometimes you come up with the perfect query to solve a particular problem, then you forget about it and next time you have to reinvent the wheel. Well, no more. I have decided to start saving these snippets so I can find them and re-use them.

In this case I was trying to find out if there was any table in my database that had a foreign key to the FilterTemplateId column of the FilterTemplate table. This method is not fool-proof, and it works only when the keys are GUIDs, but it’s better than nothing…

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_ag_show_fkeys]
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @Cursor		CURSOR
	DECLARE @Table		NVARCHAR(160)
	DECLARE @Column		NVARCHAR(160)
	DECLARE @SqlQuery	NVARCHAR(MAX)
	DECLARE @InClause	NVARCHAR(MAX)

	/* Configure this next line here */
	SET @InClause = '(SELECT FilterTemplateId FROM FilterTemplate)'
	/* End of configuration region */

	SELECT
		Col.TABLE_NAME, Col.COLUMN_NAME
	INTO
		[#AllIdColumns]
	FROM
		INFORMATION_SCHEMA.COLUMNS AS Col INNER JOIN
		INFORMATION_SCHEMA.TABLES AS Tab ON Col.TABLE_NAME = Tab.TABLE_NAME
	WHERE
			Col.DATA_TYPE = 'uniqueidentifier'
		AND Tab.TABLE_TYPE = 'BASE TABLE'

	CREATE TABLE #AllReferences(TableName NVARCHAR(160), ColumnName NVARCHAR(160), Id UNIQUEIDENTIFIER)

	SET @Cursor = CURSOR for
		SELECT
			TABLE_NAME,
			COLUMN_NAME
		FROM
		[#AllIdColumns]

	OPEN @Cursor
	FETCH NEXT FROM @Cursor INTO
		@Table,
		@Column

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SqlQuery = 'INSERT INTO #AllReferences(TableName, ColumnName, Id)'
		SET @SqlQuery = @SqlQuery + ' SELECT DISTINCT ''' + @Table + ''', ''' + @Column + ''', [' + @Column + ']'
		SET @SqlQuery = @SqlQuery + ' FROM [' + @Table + '] WHERE [' + @Column + '] IN '
		SET @SqlQuery = @SqlQuery + @InClause

		EXECUTE(@SqlQuery)

		FETCH NEXT FROM @Cursor INTO
			@Table,
			@Column
	END
	CLOSE @Cursor
	DEALLOCATE @Cursor

	SELECT * FROM #AllReferences

END
GO

EXECUTE [dbo].[usp_ag_show_fkeys]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ag_show_fkeys]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp_ag_show_fkeys]

GO

Alberto “Under-the-bonnet” Gemin

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: