Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate Repair Statements for Not-Trusted Foreign Keys


Generate Repair Statements for Not-Trusted Foreign Keys

Author
Message
The Wizard Of Oz
The Wizard Of Oz
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 314
Comments posted to this topic are about the item Generate Repair Statements for Not-Trusted Foreign Keys
bthomson
bthomson
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 69
-- I find it more useful
-- to put Sql Browser in text mode...
-- copy code out of window
-- and run it.
--
-- I did this by allowing it to continue after it finds an error...(added a go)
-- I also added '--' to the Column header so that it will be ignored when the code is run.
--
--Generates repair statements for not-trusted foreign keys
sp_MSforeachdb'
IF EXISTS(SELECT 1 FROM [?].sys.foreign_keys WHERE is_not_trusted = 1)
BEGIN
SELECT
''go
ALTER TABLE [?].[''+s.name+''].[''+o.name+''] WITH CHECK
CHECK CONSTRAINT [''+fk.name+'']'' AS [--CheckForeignKeyCommand]
FROM [?].sys.foreign_keys fk
INNER JOIN [?].sys.objects o ON fk.parent_object_id = o.object_id
AND fk.is_not_trusted = 1
AND fk.is_not_for_replication = 0
INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id
END'
The Wizard Of Oz
The Wizard Of Oz
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 314
Very nice tricks!

I think I'll use your version in the future,
makes it easy to run all the returned statements at once :-D
Tony Fountain
Tony Fountain
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 202
I have a little utility very similar:


SET NOCOUNT ON;
GO

DECLARE @UntrustedConstraints TABLE (
DatabaseName SYSNAME NOT NULL,
SchemaName SYSNAME NOT NULL,
TableName SYSNAME NOT NULL,
ConstraintName SYSNAME NOT NULL,
IsDisabled BIT NOT NULL,
ConstraintType VARCHAR(2) NOT NULL,
AlterSQL AS (
'USE ' + QUOTENAME(DatabaseName)
+ '; ALTER TABLE ' + QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
+ ' WITH CHECK '
+ CASE WHEN IsDisabled = 1 THEN 'NO' ELSE '' END
+ 'CHECK CONSTRAINT ' + QUOTENAME(ConstraintName) + ';'
+ CHAR(13) + CHAR(10) + 'GO'
)
);

DECLARE @sql NVARCHAR(MAX) = '
USE [?];
IF DB_NAME() IN (''master'', ''model'', ''msdb'', ''tempdb'') RETURN;
WITH UntrustedConstraints (DatabaseName, SchemaName, TableName, ConstraintName, IsDisabled, ConstraintType)
AS (
SELECT DB_NAME(), s.name, t.name, i.name, is_disabled, ''FK''
FROM sys.foreign_keys i
INNER JOIN sys.tables t
ON i.parent_object_id = t.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
AND t.is_ms_shipped = 0
UNION
SELECT DB_NAME(), s.name, t.name, c.name, is_disabled, ''C''
FROM sys.check_constraints c
INNER JOIN sys.tables t
ON c.parent_object_id = t.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE c.is_not_trusted = 1
AND c.is_not_for_replication = 0
AND t.is_ms_shipped = 0
)
SELECT DatabaseName, SchemaName, TableName, ConstraintName, IsDisabled, ConstraintType
FROM UntrustedConstraints;
';

INSERT INTO @UntrustedConstraints (DatabaseName, SchemaName, TableName, ConstraintName, IsDisabled, ConstraintType)
EXEC sp_MSforeachdb @command1=@sql;

SELECT * FROM @UntrustedConstraints
ORDER BY IsDisabled DESC, DatabaseName, SchemaName, TableName, ConstraintName;



This also addresses check constraints. I just run it in grid mode, copy the statements, and do a regex replace all to get the GO on a separate line.
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12376 Visits: 885
Thanks for the script.
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12376 Visits: 885
Thanks for the improvements from other commenters.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search