SQL Clone
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
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

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

Group: General Forum Members
Points: 12 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
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 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
SSChasing Mays
SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)SSChasing Mays (637 reputation)

Group: General Forum Members
Points: 637 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
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29522 Visits: 885
Thanks for the script.
Iwas Bornready
Iwas Bornready
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29522 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