Last active
September 18, 2024 19:30
-
-
Save ArtemAvramenko/d630c65bfb8576aa1100e5c92bc722ee to your computer and use it in GitHub Desktop.
The T/SQL script solves the problem with is_not_trusted indexes after moving data to another Azure db without using the sp_MSforeachtable proc
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE | |
@Schema NVARCHAR(MAX), | |
@Name NVARCHAR(MAX), | |
@Sql NVARCHAR(MAX) | |
DECLARE TableCursor CURSOR FOR | |
SELECT TABLE_SCHEMA, TABLE_NAME | |
FROM INFORMATION_SCHEMA.TABLES | |
WHERE TABLE_TYPE = 'BASE TABLE' | |
OPEN TableCursor | |
WHILE 1 = 1 | |
BEGIN | |
FETCH NEXT FROM TableCursor INTO @Schema, @Name | |
IF @@FETCH_STATUS <> 0 BREAK | |
SET @Sql = 'ALTER TABLE [{Schema}].[{Name}] WITH CHECK CHECK CONSTRAINT ALL' | |
SET @Sql = REPLACE(@SQL, '{Schema}', @Schema); | |
SET @Sql = REPLACE(@SQL, '{Name}', @Name); | |
RAISERROR(@Sql, 0, 1) WITH NOWAIT | |
EXECUTE sp_executesql @Sql | |
END | |
CLOSE TableCursor | |
DEALLOCATE TableCursor |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment