How to make table truncation query as dynamic SQL

By using the following dynamic query you can truncate the required tables:

DECLARE @DynamicSQL AS VARCHAR(MAX) = '';

SELECT @DynamicSQL = @DynamicSQL + 'TRUNCATE TABLE ' + QUOTENAME(NAME) + '; '
FROM sys.tables
WHERE NAME LIKE '%RND%';

--PRINT @DynamicSQL
EXEC (@DynamicSQL)

For the second query the dynamic query is :

DECLARE @DynamicSQL1 AS VARCHAR(MAX) = '';

SELECT @DynamicSQL1 = @DynamicSQL1 + 'INSERT INTO ' + QUOTENAME(NAME) + ' SELECT * FROM ' + QUOTENAME(NAME) + '; '
FROM sys.tables
WHERE NAME LIKE '%RND%';

--PRINT @DynamicSQL1
EXEC (@DynamicSQL1)

This might be what you are looking for

All statements are collected in a table, ordered by their point of insert (id is IDENTITY). It will be - for sure - no problem for you to build your second statement in the same way...

DECLARE @cmdTbl TABLE(id INT IDENTITY,cmd NVARCHAR(MAX));

INSERT INTO @cmdTbl(cmd) 
SELECT 'TRUNCATE TABLE ' + QUOTENAME(t.TABLE_CATALOG) + '.' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ';'
FROM INFORMATION_SCHEMA.TABLES  AS t
WHERE t.TABLE_NAME LIKE '%RND%'

--After collecting all statements I use a CURSOR to execute them one after the other.

DECLARE @cmd VARCHAR(MAX);
DECLARE cur CURSOR FOR SELECT cmd FROM @cmdTbl ORDER BY id;
OPEN cur;

FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    --EXEC(@cmd); --For syntax check you start without EXEC...
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

Step 1:

vi truncate_count.sql 
use dbname
go
---generate truncate table statements 
select 'Truncate Table '+''+convert(varchar(30),o.name)+'
go' as table_name 
from sysobjects o
where type = 'U'
order by table_name
go 

---check the table count 
select 'select count(1) from  '+''+convert(varchar(30),o.name)+'
go' AS table_name
from sysobjects o
where type = 'U'
order by table_name
go

Step 2:

isql -U<USER> -S<server> -P<pwd> -w999 -D<dbname> -e -itruncate_count.sql  -otruncate_count1.sql 

Step 3: Add the below lines at the starting of the file truncate_count1.sql

use dbname
go 

isql -U<USER> -S<server> -P<pwd> -w999 -D<dbname> -e -itruncate_count1.sql  -otruncate_count1.log