Skip to content

Instantly share code, notes, and snippets.

@ArtemAvramenko
Last active March 29, 2024 11:11
Show Gist options
  • Save ArtemAvramenko/d630c65bfb8576aa1100e5c92bc722ee to your computer and use it in GitHub Desktop.
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
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