Delete all Tables based on creation date

--Delete all Tables based on creation date



 DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR FOR
SELECT name AS tname
FROM sys.objects
WHERE create_date < GETDATE() - 1-- Days old

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @sql = 'DROP TABLE ' + @tname
       --EXEC (@sql)  -- For Executing
       PRINT @sql --For Printing

       FETCH NEXT FROM db_cursor INTO @tname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Simplest way to Truncate/Delete all tables in a given database.

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSForEachTable 'Truncate TABLE ?' --- For Truncating all tables

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  --- For Deleting all tables

Delete Statement running forever/slow in SQL Server

Things that can cause a delete to be slow:

• cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
• Transaction log needing to grow
• Many Foreign keys to check
• deleting a lot of records
• many indexes
• deadlocks and blocking
• triggers

Script to delete millions of records without increasing your log size in SQL Server

DECLARE @continue INT
DECLARE @rowcount INT

SET @continue = 1

WHILE @continue = 1
BEGIN
 PRINT GETDATE()

 SET ROWCOUNT 10000     --Replace 10000 as required


 BEGIN TRANSACTION

 DELETE
 FROM dbo.Transactions
 WHERE TranDate IS NULL --Replace your delete script here

 SET @rowcount = @@rowcount

 COMMIT

 PRINT GETDATE()

 IF @rowcount = 0
 BEGIN
  SET @continue = 0
 END
END