Search This Blog

How to delete/drop all the tables

Note: ---- For Delete all tales enable below
SET @statement = 'Delete TABLE ' + @Nombre;
--- For Drop all tales enable below
SET @statement = 'DROP TABLE ' + @Nombre;
use jiradb
go
DECLARE @Nombre NVARCHAR(MAX);
DECLARE curso CURSOR FAST_FORWARD 
FOR 
Select Object_name(object_id) AS Nombre from sys.objects where type = 'U'

OPEN curso
FETCH NEXT FROM curso INTO @Nombre

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
DECLARE @statement NVARCHAR(200);
---- For Delete all tales enable below
SET @statement = 'Delete TABLE ' + @Nombre;
--- For Drop all tales enable below
SET @statement = 'DROP TABLE ' + @Nombre;
print @statement
execute sp_executesql @statement;
END
FETCH NEXT FROM curso INTO @Nombre
END
CLOSE curso
DEALLOCATE curso

Remove all functions ,constrains, tables,Procedures fro Database



/* 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] > @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] > @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] > @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 <> @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 <> @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] > @name ORDER BY [name])
END
GO

Truncate All Tables - Part I



This procedure will truncate all the tables in the database
To truncate all tables,I have seen the programmers using Cursors to get all tables and truncate them.This will avoid Cursors. Running this procedure will make SQL Server to slow down other tasks. So test this with test server and make sure that you are not running any other SQL Server related processes 





Create Procedure Truncate_All_Tables
as



Declare @t table(query varchar(1000),tables varchar(50))



Insert into @t 



    select 'Truncate table ['+T.table_name+']', T.Table_Name from 



    INFORMATION_SCHEMA.TABLES T



    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC



    on T.table_name=TC.table_name 



    where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and 



    T.table_name not in ('dtproperties','sysconstraints','syssegments') and     Table_type='BASE TABLE' 



Insert into @t 



    select 'delete table ['+T.table_name+']', T.Table_Name from  INFORMATION_SCHEMA.TABLES T



    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC



    on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and 



    T.table_name <>'dtproperties'and Table_type='BASE TABLE' 



Declare @sql varchar(8000)



Select @sql=IsNull(@sql+' ','')+ query from @t



Exec(@sql)



GO



Run this procedure as



EXEC Truncate_All_Tables



Truncate All Tables - Part II




statements. This is other method






Set NoCount ON






Declare @tableName varchar(200)



set @tableName=''





While exists





( 





--Find all child tables and those which have no relations






    select T.table_name from INFORMATION_SCHEMA.TABLES T





    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC





    on T.table_name=TC.table_name 




    where (TC.constraint_Type ='Foreign Key'or TC.constraint_Type is NULL) and





    T.table_name not in ('dtproperties','sysconstraints','syssegments')and 




    Table_type='BASE TABLE' and T.table_name > @TableName)






Begin






    Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T





    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC





    on T.table_name=TC.table_name 




    where (TC.constraint_Type ='Foreign Key'or TC.constraint_Type is NULL) and 




    T.table_name not in ('dtproperties','sysconstraints','syssegments') and 




    Table_type='BASE TABLE' and T.table_name > @TableName


    --Truncate the table






    Exec('Truncate table '+@tablename)







End







set @TableName=''






While exists





( 




--Find all Parent tables 






    select T.table_name from INFORMATION_SCHEMA.TABLES T





    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC





    on T.table_name=TC.table_name 




    where TC.constraint_Type ='Primary Key'and T.table_name <>'dtproperties' and 




    Table_type='BASE TABLE' and T.table_name > @TableName )






Begin






    Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T





    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name 




    where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties' and 




    Table_type='BASE TABLE' and T.table_name > @TableName 










    --Delete the table





    Exec('Delete from '+ @tableName) 










    --Reset identity column 




    If exists




    ( 




        select * from information_schema.columns 




        where COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema)+'.'+




        QUOTENAME(@tableName)), column_name,'IsIdentity')=1




    ) 




    DBCC CHECKIDENT (@tableName, RESEED, 1)






End





Set NoCount Off