SQL Server: Delete All the Rows of All the Tables

sql server: delete all the rows of all the tables

If you don't want to script and drop the tables, there are a number of ways to do this with a loop. Here's probably the easiest:

sp_MsForEachTable 'TRUNCATE TABLE ?'

SQL query to delete all rows from tables where the table name contains a certain string

So, after combining the code from the possible duplicate question, and the answer from Greg, here's the result that worked best:

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'truncate table ' + quotename(OBJECT_SCHEMA_NAME(object_id)) + '.' + 
quotename(name)
FROM sys.tables
WHERE name like '%PMO%'
ORDER BY name;

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

Thanks to everyone for your help!!!

Delete rows from all tables

The easiest way may be the following:

SELECT 
CONCAT('DELETE FROM ',TABLE_NAME," WHERE column1 = 'abc';") comd
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
AND COLUMN_NAME ='column1';

This query will give you output like below:

DELETE FROM TABLE_1 WHERE column1 = 'abc';
DELETE FROM TABLE_2 WHERE column1 = 'abc';
DELETE FROM TABLE_3 WHERE column1 = 'abc';
DELETE FROM TABLE_4 WHERE column1 = 'abc';
.
.
.

Now copy these DELETE commands and execute all.

---

Note:
In other way, you can write a stored program where you can turn these generated command strings into executable command/query through prepare statement.

But you can prefer the easiest way I've suggested above in order to bypass complexity.

How can I delete all dependent rows of a table?

You Can Use Cascade Cascading Referential Integrity Constraints

Update:
you should enable Cascading Referential Integrity Constraints of Table A (PK) from Table B where ID of A is foreign Key and similarly of PK of Table B From Table C Where ID of B is foreign Key

MSDN LIBRARY

CODE PROJECT ARTICALE

Very Nice Article BLOG.SQL AUTHORITY

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the list of tables. Generate Drop scripts in the select statement and drop it using Dynamic SQL:

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(TABLE_SCHEMA) + '.'+ QUOTENAME(TABLE_NAME) + '; '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'

Exec Sp_executesql @sql

Sys.Tables Version

DECLARE @sql NVARCHAR(max)=''

SELECT @sql += ' Drop table ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(t.NAME) + '; '
FROM   sys.tables t
       JOIN sys.schemas s
         ON t.[schema_id] = s.[schema_id]
WHERE  t.type = 'U'

Exec sp_executesql @sql

Note: If you have any foreign Keys defined between tables then first run the below query to disable all foreign keys present in your database.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

For more information, check here.

---

Related Topics