Facebook
From Jittery Matamata, 6 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 245
  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