November 9, 2016 at 10:58 pm
Hi!
I need to delete all tables listed in a table named dbo.tblSum. There are 500 tables that I need to delete.
I am not sure on how to do that by using loop and calling each table from a table and delete them. Can someone please help?
Thank you
DBA newbie
November 9, 2016 at 11:06 pm
whitesql (11/9/2016)
Hi!I need to delete all tables listed in a table named dbo.tblSum. There are 500 tables that I need to delete.
I am not sure on how to do that by using loop and calling each table from a table and delete them. Can someone please help?
Thank you
DBA newbie
Can you please post the DDL (create table) script for the table and some sample data as an insert statement please?
😎
If I understand the question correctly, you want to drop all tables in a database where the name of the table exists as a column value in the table dbo.tblSum, is that right?
November 9, 2016 at 11:35 pm
hi Eirikur!!!
Yes, that is correct but not drop the table only delete the rows of each table.
here are the sample:
CREATE TABLE dbo.AllTables
(
DatabaseName nvarchar(50),
TableName nvarchar(50),
ColumnName1 nvarhcar(50),
ColumnName1 nvarchar(50)
)
INSERT INTO dbo.AllTables VALUES (‘HumanResources’,’HRDept’,’GroupName’,’Schedule’)
INSERT INTO dbo.AllTables VALUES(‘Sales’,’SalesCreditCard’,’CardType’,’CardNumber’)
CREATE TABLE dbo.HRDept
(
ID int,
GroupName nvarchar(50),
Schedule nvarchar(50)
)
INSERT INTO dbo.HRDept VALUES(1, ‘IT’,’’PM shift’)
CREATE TABLE dbo.SalesCreditCard
(
CardType varchar(50),
CardNumber varchar(50)
)
INSERT TABLE INTO dbo.SalesCreditCard VALUES(‘mastercard’,’004’)
Thank you!
November 10, 2016 at 12:08 am
Here is a very simple method that first tries to truncate the table and if that fails, then it deletes all rows from the table.
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.AllTables') IS NOT NULL DROP TABLE dbo.AllTables;
CREATE TABLE dbo.AllTables
(
DatabaseName nvarchar(50),
TableName nvarchar(50),
ColumnName1 nvarchar(50),
ColumnName2 nvarchar(50)
)
INSERT INTO dbo.AllTables VALUES ('HumanResources','HRDept','GroupName','Schedule')
INSERT INTO dbo.AllTables VALUES('Sales','SalesCreditCard','CardType','CardNumber')
IF OBJECT_ID(N'dbo.HRDept') IS NOT NULL DROP TABLE dbo.HRDept;
CREATE TABLE dbo.HRDept
(
ID int,
GroupName nvarchar(50),
Schedule nvarchar(50)
)
INSERT INTO dbo.HRDept VALUES(1, 'IT','PM shift')
IF OBJECT_ID(N'dbo.SalesCreditCard') IS NOT NULL DROP TABLE dbo.SalesCreditCard;
CREATE TABLE dbo.SalesCreditCard
(
CardType varchar(50),
CardNumber varchar(50)
)
INSERT INTO dbo.SalesCreditCard VALUES('mastercard','004');
-----------------------------------------------------------------------------------
DECLARE @DELETE_TEMPLATE NVARCHAR(MAX) = N'
USE {{@DATABASENAME}}
BEGIN TRY
TRUNCATE TABLE dbo.{{@TABLENAME}};
END TRY
BEGIN CATCH
DELETE FROM dbo.{{@TABLENAME}};
END CATCH
';
DECLARE @DELETE_SQL NVARCHAR(MAX) =
(
SELECT
REPLACE(REPLACE(@DELETE_TEMPLATE
,N'{{@TABLENAME}}'
,QUOTENAME(ATAB.TableName)
)
,N'{{@DATABASENAME}}'
,QUOTENAME(ATAB.DatabaseName)
)
FROM dbo.AllTables ATAB
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
SELECT @DELETE_SQL;
-- UNCOMMENT THIS LINE TO EXECUTE
-- THE GENERATED CODE.
-----------------------------------------------------------------------------------
-- EXEC (@DELETE_SQL);
-----------------------------------------------------------------------------------
-- CHECK THE RESULTS
SELECT * FROM Sales.dbo.SalesCreditCard;
SELECT * FROM HumanResources.dbo.HRDept;
-----------------------------------------------------------------------------------
November 10, 2016 at 5:48 am
Hi Eirikur!!
Thank you so much! It worked! 🙂
November 10, 2016 at 6:17 am
Hi Eirikur,
What if I want to delete the rows of a table per batch like for example I want to delete first 500 records so that it will not eat a lot of resources if I have millions of records then delete 2nd batch 500 records until all the records are deleted. Can I also do that using your code? Thank you!
November 10, 2016 at 6:25 am
whitesql (11/10/2016)
Hi Eirikur,What if I want to delete the rows of a table per batch like for example I want to delete first 500 records so that it will not eat a lot of resources if I have millions of records then delete 2nd batch 500 records until all the records are deleted. Can I also do that using your code? Thank you!
Do you want to keep the truncate option and do the delete if truncate fails?
😎
November 10, 2016 at 6:32 am
whitesql (11/10/2016)
for example I want to delete first 500 records so that it will not eat a lot of resources if I have millions of records
TRUNCATE, which is what was used in the earlier code, doesn't use a lot of resources because all it does is deallocate the pages assigned to the table.
Do you want to switch to the more expensive DELETE do that you can do it in batches?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2016 at 6:38 am
I want to use the delete. 😀
November 10, 2016 at 7:24 am
whitesql (11/10/2016)
I want to use the delete. 😀
I understand that using a delete is a business requirement. Do all the tables have a unique clustered index or a single column primary key? Or do some have column combination primary key?
😎
A generic solution is not going to be the most efficient unless the variations are few, can you change the schema to include the clustered index column name if such an index exists?
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply