Comments
Sort by recent activity
Thanks. / comments
Thanks.
Sorry I left a hard coded schema name 'mmrs.' in there, you will need to remove it. / comments
Sorry I left a hard coded schema name 'mmrs.' in there, you will need to remove it.
I use this script to fix all the NO CHECKS in the database.
I'm about to post a related question called "Disabling foreign keys" for those interested. BEGIN TRY
BEGIN TRANSACTION
DECLARE @FK_NAME AS NVARCHAR(MAX)
DECLARE @TABLE_NAME AS NVARCHAR(MAX)
DECLARE @SQL AS NVARCHAR(MAX)
DECLARE CFks CURSOR FOR
SELECT
[name] fk_name,
'mmrs.' + OBJECT_NAME([parent_object_id]) table_name
FROM
sys.foreign_keys fk
WHERE
is_not_trusted=1
OPEN CFks
FETCH NEXT FROM CFks
INTO @FK_NAME, @TABLE_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'FK: ' + @FK_NAME + ' TABLE: ' + @TABLE_NAME
SET @SQL = '
ALTER TABLE ' + @TABLE_NAME + '
WITH CHECK
CHECK CONSTRAINT ' + @FK_NAME + '
'
PRINT @SQL
EXEC sp_executesql @SQL
FETCH NEXT FROM CFks
INTO @FK_NAME, @TABLE_NAME
END
CLOSE CFks
DEALLOCATE CFks
PRINT 'Done.'
COMMIT TRANSACTION
PRINT 'Committed.'
END TRY
BEGIN CATCH
PRINT 'Error.'
PRINT 'FK: ' + @FK_NAME + ' TABLE: ' + @TABLE_NAME
ROLLBACK TRANSACTION
PRINT 'Rolled back.'
END CATCH
/ comments
I use this script to fix all the NO CHECKS in the database.
I'm about to post a related question called "Disabling foreign keys" for those interested.BEGIN TRY
BEGIN TRANSACTION
DECLARE @FK_NAME ...