delete all tables using sqlcmd

Question

Post reply

delete all tables using sqlcmd

whitesql

Ten Centuries

Points: 1237
More actions
November 16, 2016 at 5:01 pm

#337385

Hi guys,
Can someone please help me to convert below tsql script to sqlcmd?
I need to connect to a server and instance and delete all tables in a database where the name of the table exists as a column value in the table dbo.tblSum and run it on sqlcmd.
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.tblSum ATAB
FOR XML PATH(''),TYPE
).value('(./text())[1]','NVARCHAR(MAX)');
SELECT @DELETE_SQL;
EXEC (@DELETE_SQL);
Thank you so much in advance!
DBA newbie

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

whitesql Ten Centuries Points: 1237 More actions · Answer 1

whitesql

Ten Centuries

Points: 1237

November 16, 2016 at 5:05 pm

#1912938

* I need to delete all rows of all tables.

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 2

whitesql (11/16/2016)
* I need to delete all rows of all tables.

It would be easier on you and the system to create the scripts for all the tables, drop the tables, and rebuild them from the scripts. Just trying to delete rows is going to be slow, bloat the log file, be slow, not always work because of dependencies, be slow, fire all delete triggers, and be slow.

Also...

1. Why do you need to do this through SQLCMD and

2. Why do you need to do this at all?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 77012 More actions · Answer 3

I agree with most of what Jeff said, but I would TRUNCATE the tables rather than scripting, dropping, and recreating them.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 4

drew.allen (11/17/2016)
I agree with most of what Jeff said, but I would TRUNCATE the tables rather than scripting, dropping, and recreating them.
Drew

Don't forget... If they have FKs on them, TRUNCATE is out of the question unless you drop or disable the FKs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

delete all tables using sqlcmd

Cookies on SQLServerCentral