document.write('
Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1. /* Drop all non-system stored procs */
  2. DECLARE @name VARCHAR(128)
  3. DECLARE @SQL VARCHAR(254)
  4.  
  5. SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'P\' AND category = 0 ORDER BY [name])
  6.  
  7. WHILE @name IS NOT NULL
  8. BEGIN
  9.     SELECT @SQL = \'DROP PROCEDURE [dbo].[\' + RTRIM(@name) +\']\'
  10.     EXEC (@SQL)
  11.     PRINT \'Dropped Procedure: \' + @name
  12.     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'P\' AND category = 0 AND [name] > @name ORDER BY [name])
  13. END
  14. GO
  15.  
  16. /* Drop all views */
  17. DECLARE @name VARCHAR(128)
  18. DECLARE @SQL VARCHAR(254)
  19.  
  20. SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'V\' AND category = 0 ORDER BY [name])
  21.  
  22. WHILE @name IS NOT NULL
  23. BEGIN
  24.     SELECT @SQL = \'DROP VIEW [dbo].[\' + RTRIM(@name) +\']\'
  25.     EXEC (@SQL)
  26.     PRINT \'Dropped View: \' + @name
  27.     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'V\' AND category = 0 AND [name] > @name ORDER BY [name])
  28. END
  29. GO
  30.  
  31. /* Drop all functions */
  32. DECLARE @name VARCHAR(128)
  33. DECLARE @SQL VARCHAR(254)
  34.  
  35. SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] IN (N\'FN\', N\'IF\', N\'TF\', N\'FS\', N\'FT\') AND category = 0 ORDER BY [name])
  36.  
  37. WHILE @name IS NOT NULL
  38. BEGIN
  39.     SELECT @SQL = \'DROP FUNCTION [dbo].[\' + RTRIM(@name) +\']\'
  40.     EXEC (@SQL)
  41.     PRINT \'Dropped Function: \' + @name
  42.     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] IN (N\'FN\', N\'IF\', N\'TF\', N\'FS\', N\'FT\') AND category = 0 AND [name] > @name ORDER BY [name])
  43. END
  44. GO
  45.  
  46. /* Drop all Foreign Key constraints */
  47. DECLARE @name VARCHAR(128)
  48. DECLARE @CONSTRAINT VARCHAR(254)
  49. DECLARE @SQL VARCHAR(254)
  50.  
  51. SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' ORDER BY TABLE_NAME)
  52.  
  53. WHILE @name IS NOT NULL
  54. BEGIN
  55.     SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
  56.     WHILE @CONSTRAINT IS NOT NULL
  57.     BEGIN
  58.         SELECT @SQL = \'ALTER TABLE [dbo].[\' + RTRIM(@name) +\'] DROP CONSTRAINT [\' + RTRIM(@CONSTRAINT) +\']\'
  59.         EXEC (@SQL)
  60.         PRINT \'Dropped FK Constraint: \' + @CONSTRAINT + \' on \' + @name
  61.         SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' AND CONSTRAINT_NAME <> @CONSTRAINT AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
  62.     END
  63. SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'FOREIGN KEY\' ORDER BY TABLE_NAME)
  64. END
  65. GO
  66.  
  67. /* Drop all Primary Key constraints */
  68. DECLARE @name VARCHAR(128)
  69. DECLARE @CONSTRAINT VARCHAR(254)
  70. DECLARE @SQL VARCHAR(254)
  71.  
  72. SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' ORDER BY TABLE_NAME)
  73.  
  74. WHILE @name IS NOT NULL
  75. BEGIN
  76.     SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
  77.     WHILE @CONSTRAINT IS NOT NULL
  78.     BEGIN
  79.         SELECT @SQL = \'ALTER TABLE [dbo].[\' + RTRIM(@name) +\'] DROP CONSTRAINT [\' + RTRIM(@CONSTRAINT)+\']\'
  80.         EXEC (@SQL)
  81.         PRINT \'Dropped PK Constraint: \' + @CONSTRAINT + \' on \' + @name
  82.         SELECT @CONSTRAINT = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' AND CONSTRAINT_NAME <> @CONSTRAINT AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
  83.     END
  84. SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = \'PRIMARY KEY\' ORDER BY TABLE_NAME)
  85. END
  86. GO
  87.  
  88. /* Drop all tables */
  89. DECLARE @name VARCHAR(128)
  90. DECLARE @SQL VARCHAR(254)
  91.  
  92. SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'U\' AND category = 0 ORDER BY [name])
  93.  
  94. WHILE @name IS NOT NULL
  95. BEGIN
  96.     SELECT @SQL = \'DROP TABLE [dbo].[\' + RTRIM(@name) +\']\'
  97.     EXEC (@SQL)
  98.     PRINT \'Dropped Table: \' + @name
  99.     SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [TYPE] = \'U\' AND category = 0 AND [name] > @name ORDER BY [name])
  100. END
  101. GO
');