Sql-server – Using dynamic query to loop through Db for a truncate statement

sql servertruncate

Please, any help with my SQL query below will be appreciated. I am not familiar with how to loop through DBs to truncate a table. What I have tried is below:

DECLARE @DynamicSQL AS VARCHAR(MAX) = '';
DECLARE @dbName     nvarchar(1000) ='SP_%%';


SELECT @DynamicSQL = @DynamicSQL + 'Use' + QUOTENAME(NAME) + ';  '
FROM sys.databases
WHERE NAME LIKE  'SP_%%';

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


PRINT @DynamicSQL
--EXEC (@DynamicSQL)

The Result I get is below:

Use[SP__20180314];  Use[SPAUT__06092018];

But I want something like:

Use[SP_20180314] Truncate Table log;  Use[SPAUT_06092018] Truncate Table log;

I am trying to truncate log tables from my databases on SQL Server 2016.
Thanks

Best Answer

As Jacob H mentioned in the comments, you'll need to query sys.tables with the appropriate USE statement for each database, or by explicitly using the three-part syntax {database}.sys.tables for database.

One way to do this is to query all databases, and while doing so aggregate a string that queries sys.tables for that database. Since you didn't specify a version of SQL Server, I'll use the STRING_AGG() function that is now available in SQL Server 2017. On earlier SQL Server versions, you can use your choice of methods, such as the (officially undocumented and unsupported) variable concatenation used for @DynamicSQL in your question.

-- Print the first 8,000 bytes of TRUNCATE TABLE statements for each database
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = 'DECLARE @truncateTableSql NVARCHAR(MAX)
        ' + STRING_AGG('
        USE ' + CONVERT(NVARCHAR(MAX), QUOTENAME(name)) + '
        SET @truncateTableSql = NULL
        SELECT @truncateTableSql = STRING_AGG(
            CONVERT(NVARCHAR(MAX),''TRUNCATE TABLE '') +
            QUOTENAME(DB_NAME()) + ''.'' +
            QUOTENAME(SCHEMA_NAME(schema_id)) + ''.'' +
            QUOTENAME(name), 
            CHAR(10))
        FROM sys.tables
        WHERE is_ms_shipped = 0 /* Avoid Microsoft-provided tables */
            AND name LIKE ''log%''
        -- Note that PRINT will truncate the string to the first 8,000 bytes
        PRINT(@truncateTableSql)
        --EXEC(@truncateTableSql)
    ', CHAR(10))
FROM sys.databases
WHERE state = 0 /* ONLINE */
    AND is_read_only = 0
    AND name NOT IN ('master','model','msdb','tempdb')

EXEC(@sql)

Simplified query for a single table across databases

An update makes it sounds like only the dbo.log table needs to be truncated. If you only need to target a single table with a known name, you can simplify the script quite a bit. For example:

-- Print the first 8,000 bytes of TRUNCATE TABLE statements
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = STRING_AGG('TRUNCATE TABLE ' + QUOTENAME(name) + '.dbo.log', CHAR(10))
FROM sys.databases
WHERE state = 0 /* ONLINE */
    AND is_read_only = 0
    AND name NOT IN ('master','model','msdb','tempdb')
    AND OBJECT_ID(name + '.dbo.log') IS NOT NULL
PRINT(@sql)
--EXEC(@sql)

Simplified query for a single table across databases in earlier versions of SQL Server

There are many methods for concatenating strings in SQL Server, and many blog posts explore and vigorously debate these options. I prefer STRING_AGG() if you are using SQL Server 2017 or higher, but using XML to concatenate strings is one option if you are unable to upgrade to SQL Server 2017. For example:

-- Print the first 8,000 bytes of TRUNCATE TABLE statements
-- (Comment out the PRINT and enable the EXEC if you want to execute)
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql = CONVERT(NVARCHAR(MAX), (
    SELECT CONVERT(NVARCHAR(MAX), 'TRUNCATE TABLE ') + QUOTENAME(name) + '.dbo.log; '
    FROM sys.databases
        WHERE state = 0 /* ONLINE */
        AND is_read_only = 0
        AND name NOT IN ('master','model','msdb','tempdb')
        AND OBJECT_ID(name + '.dbo.log') IS NOT NULL 
    FOR XML PATH('')
))
PRINT(@sql)
--EXEC(@sql)
Related Question