February 1, 2013 at 2:10 am
Comments posted to this topic are about the item Generate Repair Statements for Not-Trusted Foreign Keys
February 2, 2013 at 9:48 am
-- 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'
February 3, 2013 at 7:58 am
Very nice tricks!
I think I'll use your version in the future,
makes it easy to run all the returned statements at once 😀
November 7, 2014 at 2:26 pm
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.
May 2, 2016 at 4:02 pm
Thanks for the script.
May 2, 2016 at 4:03 pm
Thanks for the improvements from other commenters.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply