Drop all the tables stored procedures triggers constraints and all the dependencies in one sql statement

Question

Is there any way in which I can clean a database in SQl Server 2005 by dropping all the tables and deleting stored procedures  triggers  constraints and all the dependencies in one SQL statement   REASON FOR REQUEST   I want to have a DB script for cleaning up an existing DB which is not in use rather than creating new ones  especially when you have to put in a request to your DB admin and wait for a while to get it done

User · Answer

Another alternative if you are developing  would be  DROP SCHEMA public CASCADE  CREATE SCHEMA public

User · Answer

try this      USE DATABASE GO DECLARE  tname VARCHAR 150  DECLARE  strsql VARCHAR 300   SELECT  tname    SELECT TOP 1  name  FROM sys objects WHERE  type     U  and  name  like N TableName   ORDER BY  name    WHILE  tname IS NOT NULL BEGIN     SELECT  strsql    DROP TABLE  dbo       RTRIM  tname           EXEC   strsql      PRINT  Dropped Table        tname     SELECT  tname    SELECT TOP 1  name  FROM sys objects WHERE  type     U  AND  name  like N TableName    AND  name   gt   tname ORDER BY  name   END

User · Answer

I tried some of the script here  but they didn t work for me  as I have my tables in schemas   So I put together the following   Note that this script takes a list of schemas  and drops then in sequence   You need to make sure that you have a complete ordering in your schemas   If there are any circular dependencies  then it will fail   PRINT  Dropping whole database  GO  ------------------------------------------ -- Drop constraints ------------------------------------------ DECLARE  Sql NVARCHAR 500  DECLARE  Cursor CURSOR  SET  Cursor   CURSOR FAST FORWARD FOR SELECT DISTINCT sql    ALTER TABLE    tc2 CONSTRAINT SCHEMA         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 PRINT  Sql Exec   Sql  FETCH NEXT FROM  Cursor INTO  Sql END  CLOSE  Cursor DEALLOCATE  Cursor GO   ------------------------------------------ -- Drop views ------------------------------------------  DECLARE  sql VARCHAR MAX                  crlf VARCHAR 2    CHAR 13    CHAR 10     SELECT  sql    sql    DROP VIEW     QUOTENAME SCHEMA NAME schema id           QUOTENAME v name          crlf FROM   sys views v  PRINT  sql  EXEC  sql   GO ------------------------------------------ -- Drop procs ------------------------------------------ PRINT  Dropping all procs      GO  DECLARE  sql VARCHAR MAX                  crlf VARCHAR 2    CHAR 13    CHAR 10     SELECT  sql    sql    DROP PROC     QUOTENAME SCHEMA NAME p schema id           QUOTENAME p name          crlf FROM    sys   procedures  p  PRINT  sql  EXEC  sql   GO  ------------------------------------------ -- Drop tables ------------------------------------------ PRINT  Dropping all tables      GO EXEC sp MSForEachTable  DROP TABLE    GO  ------------------------------------------ -- Drop sequences ------------------------------------------  PRINT  Dropping all sequences      GO DECLARE  DropSeqSql varchar 1024  DECLARE DropSeqCursor CURSOR FOR SELECT DISTINCT  DROP SEQUENCE     s SEQUENCE SCHEMA         s SEQUENCE NAME     FROM INFORMATION SCHEMA SEQUENCES s  OPEN DropSeqCursor  FETCH NEXT FROM DropSeqCursor INTO  DropSeqSql  WHILE     FETCH STATUS  lt  gt  -1   BEGIN     PRINT  DropSeqSql     EXECUTE   DropSeqSql       FETCH NEXT FROM DropSeqCursor INTO  DropSeqSql END  CLOSE DropSeqCursor DEALLOCATE DropSeqCursor GO  ------------------------------------------ -- Drop Schemas ------------------------------------------   DECLARE  schemas as varchar 1000     StaticData Ird DataImport Collateral Report Cds CommonTrade MarketData TypeCode  DECLARE  schemasXml as xml   cast    lt schema gt   replace  schemas        lt  schema gt  lt schema gt      lt  schema gt    as xml   DECLARE  Sql NVARCHAR 500  DECLARE  Cursor CURSOR  SET  Cursor   CURSOR FAST FORWARD FOR SELECT sql    DROP SCHEMA    schemaName     FROM   SELECT CAST T schemaName query  text     as VARCHAR 200   as schemaName FROM  schemasXml nodes   schema   T schemaName   as X JOIN information schema schemata S on S schema name   X schemaName  OPEN  Cursor FETCH NEXT FROM  Cursor INTO  Sql  WHILE    FETCH STATUS   0  BEGIN PRINT  Sql Exec   Sql  FETCH NEXT FROM  Cursor INTO  Sql END  CLOSE  Cursor DEALLOCATE  Cursor GO

User · Answer

try this with sql2012 or above    this will be help to delete all objects by selected schema  DECLARE  MySchemaName VARCHAR 50   dbo    sql VARCHAR MAX      DECLARE  SchemaName VARCHAR 255    ObjectName VARCHAR 255    ObjectType VARCHAR 255    ObjectDesc VARCHAR 255    Category INT   DECLARE cur CURSOR FOR     SELECT   s name SchemaName   o name ObjectName   o type ObjectType  o type desc ObjectDesc  so category Category     FROM    sys objects o     INNER JOIN sys schemas s ON o schema id   s schema id     INNER JOIN sysobjects so ON so name o name     WHERE s name    MySchemaName     AND so category 0     AND o type IN   P   PC   U   V   FN   IF   TF   FS   FT   PK   TT    OPEN cur FETCH NEXT FROM cur INTO  SchemaName  ObjectName  ObjectType  ObjectDesc  Category  SET  sql     WHILE   FETCH STATUS   0 BEGIN         IF  ObjectType IN  FN    IF    TF    FS    FT   SET  sql  sql  Drop Function    MySchemaName      ObjectName CHAR 13      IF  ObjectType IN  V   SET  sql  sql  Drop View    MySchemaName      ObjectName CHAR 13      IF  ObjectType IN  P   SET  sql  sql  Drop Procedure    MySchemaName      ObjectName CHAR 13      IF  ObjectType IN  U   SET  sql  sql  Drop Table    MySchemaName      ObjectName CHAR 13       --PRINT  ObjectName            ObjectType     FETCH NEXT FROM cur INTO  SchemaName  ObjectName  ObjectType  ObjectDesc  Category END CLOSE cur      DEALLOCATE cur  SET  sql  sql CASE WHEN LEN  sql  gt 0 THEN  Drop Schema    MySchemaName CHAR 13  ELSE    END PRINT  sql EXECUTE   sql

User · Answer

One more sample declare  objectId int    objectName varchar 500    schemaName varchar 500    type nvarchar 30    parentObjId int   parentObjName nvarchar 500  declare cur cursor  for   select obj object id  s name as schema name  obj name  obj type  parent object id from sys schemas s     inner join sys sysusers u         on u uid   s principal id         JOIN         sys objects obj on obj schema id   s schema id WHERE s name    schema name  and  type    p  or obj type    v  or obj type    u  or obj type    f  or obj type    fn    order by obj type  open cur fetch next from cur into  objectId   schemaName   objectName    type   parentObjId while   fetch status   0 begin     if  type    p      begin         exec  drop procedure     schemaName           objectName            end      if  type    fn      begin         exec  drop FUNCTION     schemaName           objectName            end      if  type    f      begin         set  parentObjName    SELECT name from sys objects WHERE object id    parentObjId          exec  ALTER TABLE     schemaName           parentObjName          DROP CONSTRAINT       objectName      end      if  type    u      begin         exec  drop table     schemaName           objectName            end      if  type    v      begin         exec  drop view     schemaName           objectName            end     fetch next from cur into   objectId   schemaName   objectName    type   parentObjId end close cur deallocate cur

User · Answer

This is what I have tried   SELECT  DROP TABLE      SCHEMA NAME schema id            name       FROM sys tables   What ever the output it will print  just copy all and paste in new query and press execute  This will delete all tables

User · Answer

Try this  Select  ALTER TABLE     Table Name      drop constraint     Constraint Name  from Information Schema CONSTRAINT TABLE USAGE  Select  drop Procedure     specific name  from Information Schema Routines where specific name not like  sp   AND specific name not like  fn     Select  drop View     table name  from Information Schema tables where Table Type    VIEW   SELECT  DROP TRIGGER     name FROM sysobjects WHERE type    tr   Select  drop table     table name  from Information Schema tables where Table Type    BASE TABLE

User · Answer

I m using this script by Adam Anderson  updated to support objects in other schemas than dbo   declare  n char 1  set  n   char 10   declare  stmt nvarchar max   -- procedures select  stmt   isnull   stmt    n              drop procedure      schema name schema id            name       from sys procedures   -- check constraints select  stmt   isnull   stmt    n          alter table      schema name schema id            object name  parent object id           drop constraint      name       from sys check constraints  -- functions select  stmt   isnull   stmt    n              drop function      schema name schema id            name       from sys objects where type in    FN    IF    TF     -- views select  stmt   isnull   stmt    n              drop view      schema name schema id            name       from sys views  -- foreign keys select  stmt   isnull   stmt    n              alter table      schema name schema id            object name  parent object id        drop constraint      name       from sys foreign keys  -- tables select  stmt   isnull   stmt    n              drop table      schema name schema id            name       from sys tables  -- user defined types select  stmt   isnull   stmt    n              drop type      schema name schema id            name       from sys types where is user defined   1   exec sp executesql  stmt   Source  an Adam Anderson blog post

User · Answer

Seems like a rather dangerous feature to me  If you d implement something like this I would make sure to properly secure it in a way you won t be able to run this per accident   As suggested before you could make some sort of stored procedure yourself   In SQL Server 2005 you could have a look this system table to determine and find the objects you would like to drop   select   from sys objects

User · Answer

I d do it in two statements  DROP DATABASE      and then CREATE DATABASE

User · Answer

DECLARE  name VARCHAR 255  DECLARE  type VARCHAR 10  DECLARE  prefix VARCHAR 255  DECLARE  sql VARCHAR 255   DECLARE curs CURSOR FOR SELECT  name   xtype FROM sysobjects WHERE xtype IN   U    P    FN    IF    TF    V    TR   -- Configuration point 1 ORDER BY name  OPEN curs FETCH NEXT FROM curs INTO  name   type  WHILE   FETCH STATUS   0 BEGIN -- Configuration point 2 SET  prefix   CASE  type WHEN  U  THEN  DROP TABLE  WHEN  P  THEN  DROP PROCEDURE  WHEN  FN  THEN  DROP FUNCTION  WHEN  IF  THEN  DROP FUNCTION  WHEN  TF  THEN  DROP FUNCTION  WHEN  V  THEN  DROP VIEW  WHEN  TR  THEN  DROP TRIGGER  END  SET  sql    prefix          name PRINT  sql EXEC  sql  FETCH NEXT FROM curs INTO  name   type END  CLOSE curs DEALLOCATE curs

User · Answer

To add to Ivan s answer  I have also had the need to drop all user-defined types  so I have added this to the script      Drop all user-defined types    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    select TOP 1  name  from sys types where is user defined   1   WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP TYPE  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Type       name     SELECT  name    select TOP 1  name  from sys types where is user defined   1  END GO

User · Answer

You have to disable all triggers and constraints first   EXEC sp MSforeachtable  command1  ALTER TABLE   NOCHECK CONSTRAINT ALL   EXEC sp MSforeachtable  command1  ALTER TABLE   DISABLE TRIGGER ALL    After that you can generate the scripts for deleting the objects as  SELECT  Drop Table   name FROM sys tables WHERE type  U    SELECT  Drop Procedure   name FROM  sys procedures WHERE type  P     Execute the statements generated

User · Answer

To drop all tables   exec sp MSforeachtable  DROP TABLE      This will  of course  drop all constraints  triggers etc   everything but the stored procedures   For the stored procedures I m afraid you will need another stored procedure stored in master

User · Answer

In addition to  Ivan s answer  types all need to be included         Drop all Types    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sys types where is user defined   1 ORDER BY  name    WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP TYPE  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Type       name     SELECT  name    SELECT TOP 1  name  FROM sys types where is user defined   1 AND  name   gt   name ORDER BY  name   END GO

User · Answer

The best thing to do it is  quot Generate scripts for Drop quot  Select Database - gt  Right Click - gt  Tasks - gt  Generate Scripts - will open wizard for generating scripts after choosing objects in set Scripting option click Advanced Button  - gt  Set option  Script to create  to true  want to create   - gt  Set option  Script to Drop  to true  want to drop   - gt  Select the Check box to select objects wish to create script  - gt  Select the choice to write script  File  New window  Clipboard   It includes dependent objects by default  and will drop constraint at first  Execute the script   This way we can customize our script

User · Answer

I accidentally ran a db init script against my master database tonight  Anyways  I quickly ran into this thread  I used the  exec sp MSforeachtable  DROP TABLE    answer  but had to execute it multiple times until it didn t error  dependencies   After that I stumbled upon some other threads and pieced this together to drop all the stored procedures and functions   DECLARE mycur CURSOR FOR select O type desc schema id O name from      sys objects             O LEFT OUTER JOIN     sys extended properties E ON O object id   E major id WHERE     O name IS NOT NULL     AND ISNULL O is ms shipped  0    0     AND ISNULL E name       lt  gt   microsoft database tools support      AND   O type desc    SQL STORED PROCEDURE  OR O type desc    SQL SCALAR FUNCTION    ORDER BY O type desc O name   OPEN mycur   DECLARE  schema id int  DECLARE  fname varchar 256   DECLARE  sname varchar 256   DECLARE  ftype varchar 256    FETCH NEXT FROM mycur INTO  ftype   schema id   fname   WHILE   FETCH STATUS   0 BEGIN     SET  sname   SCHEMA NAME   schema id        IF  ftype    SQL STORED PROCEDURE          EXEC   DROP PROCEDURE       sname            fname              IF  ftype    SQL SCALAR FUNCTION          EXEC   DROP FUNCTION       sname            fname               FETCH NEXT FROM mycur INTO  ftype   schema id   fname  END  CLOSE mycur DEALLOCATE mycur  GO

User · Answer

To remove all objects in oracle     1  Dynamic  DECLARE CURSOR IX IS SELECT   FROM ALL OBJECTS WHERE OBJECT TYPE   TABLE   AND OWNER  SCHEMA NAME    CURSOR IY IS  SELECT   FROM ALL OBJECTS WHERE OBJECT TYPE  IN   SEQUENCE    PROCEDURE    PACKAGE    FUNCTION    VIEW   AND  OWNER  SCHEMA NAME    CURSOR IZ IS  SELECT   FROM ALL OBJECTS WHERE OBJECT TYPE IN   TYPE   AND  OWNER  SCHEMA NAME   BEGIN  FOR X IN IX LOOP    EXECUTE IMMEDIATE  DROP    X OBJECT TYPE       X OBJECT NAME     CASCADE CONSTRAINT     END LOOP   FOR Y IN IY LOOP    EXECUTE IMMEDIATE  DROP    Y OBJECT TYPE       Y OBJECT NAME    END LOOP   FOR Z IN IZ LOOP    EXECUTE IMMEDIATE  DROP    Z OBJECT TYPE       Z OBJECT NAME    FORCE      END LOOP  END      2 Static      SELECT  DROP TABLE       TABLE NAME       CASCADE CONSTRAINTS   FROM user tables         union ALL         select  drop    object type        object name        from user objects          where object type in   VIEW   PACKAGE   SEQUENCE    PROCEDURE    FUNCTION           union ALL         SELECT  drop             object type                          object name              force           FROM user objects         WHERE object type IN   TYPE

User · Answer

Back up a completely empty database  Instead of dropping all the objects  just restore the backup

User · Answer

There is no single statement that can be used to achieve this aim   You could of course create yourself a stored procedure that you could use to perform these various administrative tasks   You could then execute the procedure using this single statement   Exec sp CleanDatabases  DatabaseName  DBname

User · Answer

this script cleans all views  SPS  functions PKs  FKs and tables      Drop all non-system stored procs    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     P  AND category   0 ORDER BY  name    WHILE  name is not null BEGIN     SELECT  SQL    DROP PROCEDURE  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Procedure       name     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     P  AND category   0 AND  name   gt   name ORDER BY  name   END GO     Drop all views    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     V  AND category   0 ORDER BY  name    WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP VIEW  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped View       name     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     V  AND category   0 AND  name   gt   name ORDER BY  name   END GO     Drop all functions    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   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    WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP FUNCTION  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Function       name     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   gt   name ORDER BY  name   END GO     Drop all Foreign Key constraints    DECLARE  name VARCHAR 128  DECLARE  constraint VARCHAR 254  DECLARE  SQL VARCHAR 254   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   WHILE  name is not null BEGIN     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      WHILE  constraint IS NOT NULL     BEGIN         SELECT  SQL    ALTER TABLE  dbo       RTRIM  name      DROP CONSTRAINT      RTRIM  constraint               EXEC   SQL          PRINT  Dropped FK Constraint       constraint     on      name         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  lt  gt   constraint AND TABLE NAME    name ORDER BY CONSTRAINT NAME      END 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  END GO     Drop all Primary Key constraints    DECLARE  name VARCHAR 128  DECLARE  constraint VARCHAR 254  DECLARE  SQL VARCHAR 254   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   WHILE  name IS NOT NULL BEGIN     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      WHILE  constraint is not null     BEGIN         SELECT  SQL    ALTER TABLE  dbo       RTRIM  name      DROP CONSTRAINT      RTRIM  constraint              EXEC   SQL          PRINT  Dropped PK Constraint       constraint     on      name         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  lt  gt   constraint AND TABLE NAME    name ORDER BY CONSTRAINT NAME      END 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  END GO     Drop all tables    DECLARE  name VARCHAR 128  DECLARE  SQL VARCHAR 254   SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     U  AND category   0 ORDER BY  name    WHILE  name IS NOT NULL BEGIN     SELECT  SQL    DROP TABLE  dbo       RTRIM  name           EXEC   SQL      PRINT  Dropped Table       name     SELECT  name    SELECT TOP 1  name  FROM sysobjects WHERE  type     U  AND category   0 AND  name   gt   name ORDER BY  name   END GO

User · Answer

Here I found new query to delete all sp functions and triggers   declare  procName varchar 500  declare cur cursor   for select  name  from sys objects where type    p  open cur fetch next from cur into  procName while   fetch status   0 begin     exec  drop procedure      procName      fetch next from cur into  procName end close cur deallocate cur

[sql] Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql