Created
November 19, 2021 02:39
-
-
Save frandi/d9e97c8f6303809adfb8ec7e1857ef0b to your computer and use it in GitHub Desktop.
Clean Azure SQL Data Sync objects
This file contains 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
-- Triggers | |
DECLARE @TRIGGERS_SQL VARCHAR(MAX) = ( | |
SELECT | |
'DROP TRIGGER [' + SCHEMA_NAME(so.uid) + '].[' + [so].[name] + '] ' | |
FROM sysobjects AS [so] | |
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id | |
WHERE [so].[type] = 'TR' | |
AND [so].name LIKE '%_dss_%_trigger' | |
FOR XML PATH ('') | |
) | |
PRINT @TRIGGERS_SQL | |
IF LEN(@TRIGGERS_SQL) > 0 | |
BEGIN | |
EXEC (@TRIGGERS_SQL) | |
END | |
-- Tables | |
DECLARE @TABLES_SQL VARCHAR(MAX) = ( | |
SELECT | |
'DROP TABLE [' + table_schema + '].[' + table_name + '] ' | |
FROM | |
information_schema.tables where table_schema = 'DataSync' | |
FOR XML PATH ('') | |
) | |
PRINT @TABLES_SQL | |
IF LEN(@TABLES_SQL) > 0 | |
BEGIN | |
EXEC (@TABLES_SQL) | |
END | |
-- Stored Procedures | |
DECLARE @PROC_SQL VARCHAR(MAX) = ( | |
SELECT 'DROP PROCEDURE [' + routine_schema + '].[' + routine_name + '] ' | |
FROM INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'DataSync' and routine_type = 'PROCEDURE' | |
FOR XML PATH ('') | |
) | |
PRINT @PROC_SQL | |
IF LEN(@PROC_SQL) > 0 | |
BEGIN | |
EXEC (@PROC_SQL) | |
END | |
-- Types | |
DECLARE @TYPE_SQL VARCHAR(MAX) = ( | |
SELECT | |
'DROP TYPE [' + SCHEMA_NAME(so.uid) + '].[' + [so].[name] + '] ' | |
FROM systypes AS [so] | |
where [so].name LIKE '%_dss_bulktype%' | |
AND SCHEMA_NAME(so.uid) = 'Datasync' | |
FOR XML PATH ('') | |
) | |
PRINT @TYPE_SQL | |
IF LEN(@TYPE_SQL) > 0 | |
BEGIN | |
EXEC (@TYPE_SQL) | |
END | |
-- Schema | |
DROP SCHEMA DataSync |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment