Skip to content

Instantly share code, notes, and snippets.

@chrismckelt
Last active February 26, 2019 08:21
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save chrismckelt/4c90a056803e41010e0612bbe49029e3 to your computer and use it in GitHub Desktop.
Save chrismckelt/4c90a056803e41010e0612bbe49029e3 to your computer and use it in GitHub Desktop.
drop all tables from a sql database
sqlcmd -s localhost -d SupportSiteDB -E -Q "EXEC sp_msforeachtable 'ALTER TABLE [?] NOCHECK CONSTRAINT ALL'"
TIMEOUT 1
sqlcmd -s localhost -d SupportSiteDB -E -Q "DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FAST_FORWARD FOR SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']' FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql WHILE (@@FETCH_STATUS = 0) BEGIN Exec SP_EXECUTESQL @Sql FETCH NEXT FROM @Cursor INTO @Sql END CLOSE @Cursor DEALLOCATE @Cursor EXEC sp_MSForEachTable 'DROP TABLE ?' "
TIMEOUT 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment