Wiping an (Azure) SQL Server database, deleting all tables

Just for personal reference

USE [mydb]
GO

WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
  DECLARE @sql0 NVARCHAR(2000)
    SELECT TOP 1 @sql0=('ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME
    + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM information_schema.table_constraints
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

  EXEC (@sql0)
  PRINT @sql0
END

GO

DECLARE @sql1 NVARCHAR(2000)
DECLARE @sql2 NVARCHAR(2000)
WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES))
BEGIN
  DECLARE c1 CURSOR FOR
  SELECT
    ('DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') as sql1,
    ('DROP VIEW [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']') as sql2
    FROM INFORMATION_SCHEMA.TABLES
  OPEN c1
  FETCH NEXT FROM c1 INTO @sql1, @sql2
  WHILE @@FETCH_STATUS <> -1
  BEGIN
    EXEC (@sql1)
    PRINT @sql1
    EXEC (@sql2)
    PRINT @sql2
	FETCH NEXT FROM c1 INTO @sql1, @sql2
  END
  DEALLOCATE c1
END

GO

Leave a Reply

Your email address will not be published.