Advertisement
MatthijsFontys

Drop all tables mssql

Mar 18th, 2019
194
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.59 KB | None | 0 0
  1. DECLARE @SQL NVARCHAR(500) DECLARE @Cursor CURSOR
  2.  
  3. SET @Cursor = CURSOR FAST_FORWARD FOR
  4. SELECT DISTINCT SQL = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'
  5. FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
  6. LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
  7.  
  8. OPEN @Cursor FETCH NEXT FROM @Cursor INTO @SQL
  9.  
  10. WHILE (@@FETCH_STATUS = 0)
  11. BEGIN
  12. EXEC sp_executesql @SQL
  13. FETCH NEXT FROM @Cursor INTO @SQL
  14. END
  15.  
  16. CLOSE @Cursor DEALLOCATE @Cursor
  17. GO
  18.  
  19. EXEC sp_MSforeachtable 'DROP TABLE ?'
  20. GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement