Uncategorized

List Untrusted Foreign Keys for all Databases

IF OBJECT_ID('tempdb..#UntrustedFK') IS NOT NULL
	DROP TABLE #UntrustedFK;
GO

CREATE TABLE [#UntrustedFK] (
	[db_name] SYSNAME, [sch_name] SYSNAME, [ent_name] SYSNAME, [fk_name] SYSNAME
);

EXECUTE [master].[sys].[sp_MSforeachdb] 
'
	USE [?];

	INSERT INTO #UntrustedFK
	SELECT ''?''
	  ,SCHEMA_NAME(fk.schema_id) AS [schema]
	  ,[ob].[name]
	  ,[fk].[name]
	  FROM [sys].[foreign_keys] AS fk
	  JOIN [sys].[objects] AS ob
		ON [fk].[parent_object_id] = [ob].[object_id]
	  WHERE [fk].[is_not_trusted] = 1 and [fk].[is_disabled] = 0 and [fk].[is_not_for_replication] = 0;
'

SELECT * FROM [#UntrustedFK] ORDER BY db_name, sch_name, ent_name, fk_name
GO