delete all lines from all tables of a database

Question

Somebody must have already written a script to delete all lines from all tables of a database. Using DELETE command is not an option since it can takes ages on large tables. Of course there are many examples on stackoverflow and elsewhere, but they don't work with tables using foreign keys.

Basically, the script should do this :

• Store all foreign keys definition in a temporary table
• delete all foreign keys
• truncate all tables
• restore foreign keys

I think i have it :

IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
    DROP TABLE #ForeignKeys;

WITH ForeignKeys AS (
SELECT
    QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS ParentTable
    , QUOTENAME(SCHEMA_NAME(rt.schema_id)) + '.' + QUOTENAME(rt.name) AS ReferenceTable
    , QUOTENAME(f.name) AS ConstraintName
    , STUFF(Parent.Cols, 1, 1, '') AS ParentColumns
    , STUFF(Reference.Cols, 1, 1, '') AS ReferenceColumns
    , REPLACE(f.update_referential_action_desc, '_', ' ') AS UpdateAction
    , REPLACE(f.delete_referential_action_desc, '_', ' ') AS DeleteAction
FROM
    sys.tables AS t
    LEFT JOIN sys.foreign_keys AS f
        ON f.parent_object_id = t.object_id
        AND f.type = 'F'
    LEFT JOIN sys.tables AS rt
        ON f.referenced_object_id = rt.object_id
    CROSS APPLY
    (
        SELECT
            ',' + QUOTENAME(COL_NAME(fc.parent_object_id, fc.parent_column_id))AS [text()]
        FROM
            sys.foreign_key_columns AS fc
        WHERE
            fc.constraint_object_id = f.object_id
        ORDER BY
            fc.constraint_column_id
        FOR XML PATH('')
    ) Parent(Cols)
    CROSS APPLY
    (
        SELECT
            ',' + QUOTENAME(COL_NAME(fc.referenced_object_id, fc.referenced_column_id)) AS [text()]
        FROM
            sys.foreign_key_columns AS fc
        WHERE
            fc.constraint_object_id = f.object_id
        ORDER BY
            fc.constraint_column_id
        FOR XML PATH('')
    ) Reference(Cols)
)
SELECT
    ParentTable AS TableName
    , 'ALTER TABLE ' + ParentTable + ' DROP CONSTRAINT ' + ConstraintName  AS DropCmd
    , 'TRUNCATE TABLE ' + ParentTable AS TruncateCmd
    , 'ALTER TABLE ' + ParentTable + ' ADD CONSTRAINT ' + ConstraintName + ' FOREIGN KEY('
        +  ParentColumns + ') REFERENCES ' + ReferenceTable + ' (' + ReferenceColumns 
        + ') ON UPDATE ' + UpdateAction 
        + ' ON DELETE ' + DeleteAction  COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateCmd
INTO
    #ForeignKeys
FROM
    ForeignKeys
ORDER BY
 1;

-- SELECT * FROM #ForeignKeys

DECLARE @TableName SYSNAME
DECLARE @Sql NVARCHAR(MAX)

-- Drop all constraints
DECLARE FkCursor CURSOR FOR 
SELECT
    TableName
    , DropCmd
FROM
    #ForeignKeys
WHERE
    DropCmd IS NOT NULL     

OPEN FkCursor  
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @TableName + ' : ' + @sql
    EXEC sp_executesql @Sql
    FETCH NEXT FROM FkCursor INTO @TableName, @Sql
END
CLOSE FkCursor
DEALLOCATE FkCursor

-- Truncate all tables
DECLARE FkCursor CURSOR FOR 
SELECT
    TableName
    , TruncateCmd
FROM
    #ForeignKeys     

OPEN FkCursor  

FETCH NEXT FROM FkCursor INTO @TableName, @Sql
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @TableName + ' : ' + @sql
    EXEC sp_executesql @Sql
    FETCH NEXT FROM FkCursor INTO @TableName, @Sql
END
CLOSE FkCursor
DEALLOCATE FkCursor

-- Create all foreign keys
DECLARE FkCursor CURSOR FOR 
SELECT
    TableName
    , CreateCmd
FROM
    #ForeignKeys
WHERE
    CreateCmd IS NOT NULL      

OPEN FkCursor  
FETCH NEXT FROM FkCursor INTO @TableName, @Sql
WHILE @@FETCH_STATUS = 0  
BEGIN  
    PRINT @TableName + ' : ' + @sql
    EXEC sp_executesql @Sql
    FETCH NEXT FROM FkCursor INTO @TableName, @Sql
END
CLOSE FkCursor
DEALLOCATE FkCursor

DROP TABLE #ForeignKeys;

Answer 1

Right click on the object(in the object explorer) and click Script as--> drop to. Use the same method and Script as--> Create to. This should give you the scripts for Dropping as well as creating all the objects in your database. Run them in the sequence you like.

Or if you have a lot of tables and objects, you can right click on the database--> go to tasks and click on Generate Scripts.