Emptying Databases

• eduardovwc

  SSC Enthusiast

  Points: 112

  More actions

  September 20, 2005 at 7:57 pm

  #95248

  Hi Guys?

  I need to empty my database, and i have almost 20 to 30 tables, is there any statment that i can use? i am afraid i will have to delete the information table by table, which is a very long process.

  Please Help. 

• TriggerMe

  SSChampion

  Points: 11752

  More actions

  September 20, 2005 at 8:26 pm

  #591567

  Script the original Database including Stored Procedures, Views etc. Then Create a new Database and run the Script on it.

  ---

  Kindest Regards,

• Vee

  Hall of Fame

  Points: 3028

  More actions

  September 20, 2005 at 10:33 pm

  #591581

  ----AUTHOR  :  VEERESH V NASHI

  -----CREATED ON :  14-10-2005

  The following procedure shuold help u ...

  CREATE PROCEDURE prc_CleanUp_DataBase AS

  DECLARE @Cnt INT

  DECLARE @sql1 varchar(1000)

  DECLARE @key_Name varchar(100)

  DECLARE @tab_Name varchar(100)

  BEGIN

  SET NOCOUNT ON

   CREATE TABLE  #Temp1

   ( CODE INT IDENTITY(1,1),

     ATTRIB_NAME VARCHAR(100),

     TABLE_NAME  VARCHAR(100)

  &nbsp

   INSERT INTO #Temp1

   SELECT attrib.name,tab.name FROM  sysobjects Attrib JOIN SySObjects Tab ON

   attrib.parent_obJ= tab.id WHERE attrib.name LIKE 'FK_%' and tab.NAME NOT LIKE 'dtproperties'

   SELECT @Cnt = COUNT(*) FROM  #Temp1

    WHILE @Cnt > 0

     BEGIN

        SELECT @key_Name = attrib_name, @tab_Name = Table_Name from #Temp1 where CODE = @Cnt

        SET @sql1 = ' ALTER TABLE  ' + QUOTENAME( @tab_Name ,']')  + '   DROP CONSTRAINT  ' + QUOTENAME (@key_Name,']')

        PRINT 'DROPPING CONSTRAINT ' + QUOTENAME (@key_Name,']') + ' FOR THE TABLE ' + QUOTENAME( @tab_Name ,']')

        SELECT @sql1

        EXEC ( @SQL1 )

        SET @Cnt = @Cnt -1

     END

  DROP TABLE #TEMP1

   CREATE TABLE  #Temp2

   ( CODE INT IDENTITY(1,1),

     QUERY_NAME  VARCHAR(200)

  &nbsp

  INSERT INTO #Temp2

   SELECT ' DROP TABLE ' + QUOTENAME(name,']')  FROM sysobjects WHERE xtype= 'U' AND name NOT LIKE 'dtproperties'

  ---- IF U WANT TO DROP FUNCTIONS THEN UNCOMMENT THE FOLLOWING STATEMENT

   INSERT INTO #Temp2

   SELECT  ' DROP FUNCTION  ' + QUOTENAME ( name, ']')  FROM SYSOBJECTS WHERE NAME NOT LIKE 'SYS%' and NAME NOT LIKE 'dt_%' and xtype = 'FN'

  -------IF U WANT TO DROP PROCEDURE ALSO UNCOMMENT THE FOLLOWING STATEMENT

   INSERT INTO #Temp2

   SELECT ' DROP PROCEDURE ' + QUOTENAME ( name, ']')  FROM SYSOBJECTS WHERE NAME NOT LIKE 'SYS%' and NAME NOT LIKE 'dt_%' and xtype = 'P'

   SELECT @Cnt = COUNT(*) FROM  #Temp2

    WHILE @Cnt > 0

     BEGIN

        SELECT @SQL1 = QUERY_NAME from #Temp2 where CODE = @Cnt

        SELECT @sql1

        EXEC ( @SQL1 )

        SET @Cnt = @Cnt -1

     END

     DROP TABLE #TEMP2

  SET NOCOUNT OFF

  END

  

   

• Sathish Kumar. Rajaram

  Mr or Mrs. 500

  Points: 581

  More actions

  September 21, 2005 at 2:21 am

  #591616

  Simple solution, use undocumented SP sp_MSforeachtable

  Example: EXEC sp_MSforeachtable "Delete from ?"

  Where '?' is the place holder for the table name

  

  S A T ...
  Sathish's Blog

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  September 21, 2005 at 2:57 am

  #591634

  Though, if there are FK constraints declared you need to turn them off first. If there are no FK's present, you maight even be able to use truncate instead of delete.

  -- turn off FK's

  exec sp_msforeachtable 'alter table ? nocheck constraint all'

  -- delete all tables

  exec sp_msforeachtable 'delete ?'

  -- turn on FK's again ('trusted' mode)

  exec sp_msforeachtable 'alter table ? with check check constraint all'

  -- if no FK's are present

  exec sp_msforeachtable 'truncate table ?'

  /Kenneth