Skip to content

Instantly share code, notes, and snippets.

@jcorderodr

jcorderodr/objects-removal.sql

Created October 13, 2023 01:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jcorderodr/97da0150e4753d2e32cdd8d8eca1ac70 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jcorderodr/97da0150e4753d2e32cdd8d8eca1ac70 to your computer and use it in GitHub Desktop.
Download ZIP
Remove all db objects + system-versioned tables [Azure Databases compatible]
Raw
objects-removal.sql
DECLARE @sql NVARCHAR(2000)
WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
SELECT TOP 1 @sql=('ALTER TABLE ' + CONSTRAINT_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
EXEC(@sql)
PRINT @sql
END
WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.ROUTINES))
BEGIN
SELECT TOP 1 @sql=('DROP ' + ROUTINE_TYPE + ' [' + ROUTINE_NAME + ']')
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'dbo'
EXEC(@sql)
PRINT @sql
END
WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'history'))
BEGIN
SELECT TOP 1 @sql=('ALTER TABLE dbo.[' + REPLACE(TABLE_NAME,'History', '') + '] SET (SYSTEM_VERSIONING = OFF); DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'history'
EXEC(@sql)
PRINT @sql
END
WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules' AND TABLE_NAME != 'ipv6_database_firewall_rules'))
BEGIN
SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
PRINT @sql
EXEC(@sql)
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment