Home > SQL, Windows Azure > SQL Query for truncate all tables in a database in SQL/SQL Azure

SQL Query for truncate all tables in a database in SQL/SQL Azure


How to delete or truncate all the data from all the tables in a database

 For clean up the database or switch to a new database, we may need to truncate or delete all tables in the database. Its not easy to select and delete all tables in a database as the database may having n number of tables. We can get all tables in a database from sys objects and we can apply delete or truncate script to each and every table from a single cursor.

 SQL script for delete/truncate all tables in a database in SQL/SQL Azure

 The following script truncates all tables in the selected database. If you want to delete all tables in the database we can edit this query with delete statement instead of truncate statement.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('U')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN
     Set @StringVal = 'truncate table ' + @DBObjName
       exec (@StringVal)           
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName

 How to drop all stored procedures (sps) and functions from a database

The following script drop all procedures and user defined functions from a database.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('P','FN','TF')
AND Name not in ('DBM_EnableDisableAllTableConstraints')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN

      IF @Type='P'
      BEGIN
            Set @StringVal = 'Drop Procedure ' + @DBObjName
            exec (@StringVal)
      END
      ELSE IF @Type='FN' OR @Type='TF'
      BEGIN
            Set @StringVal = 'Drop Function ' + @DBObjName
            exec (@StringVal)
      END

FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName
  1. March 9, 2014 at 1:18 am

    I am not sure the place you’re getting yoour information, however great topic.
    I needs to spend some time learning more or understanding more.
    Thank youu for wonderful info I used to be in search of this info for my mission.

  2. March 9, 2014 at 7:22 am

    Heeya i aam for the fiorst time here. I came acros this board and I
    find It rezlly useful & it helped me out much. I hope to give
    something baxk and aid others like you aided me.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: