2

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

  • You must execute the query in the context of the relevant database if you want to use sys.tables like that. – Jacob H Jun 20 '18 at 18:54
5

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)
  • I am using sql 2016. I got the error STRING_AGG' is not a recognized built-in function name. Thanks – Djoe1 Jun 20 '18 at 20:41
  • @Ogbenro one or more of your databases may be in a lower compatibility level. – Erik Darling Jun 20 '18 at 20:43
  • much better than using cursor. Minor points - instead of doing exec(@sql) you should change it to print or select (since your script header tells it) and look of database_id > 4 to remove systemdbs. – Kin Shah Jun 20 '18 at 21:03
  • @Kin Good point about avoiding system databases by default; I'll make that update. All of the TRUNCATE statements do get printed by the current script (that's the PRINT inside the per-database SQL statement), so I think the comment is accurate. – Geoff Patterson Jun 20 '18 at 21:21
  • My bad .. just looked at outer but yes you have an inner PRINT(@truncateTableSql) :-) – Kin Shah Jun 20 '18 at 21:30
1

I did something similar, here's a script that will delete out all records. I've adjusted it for you below to truncate instead. Make sure to sub out the DB name where applicable.

TEST THIS IN AN ISOLATED DEV ENVIRONMENT AFTER BACKING IT UP BEFORE TESTING ANYWHERE ELSE.

-- Gather all databases on the server, store them and column information, then insert that data into a table variable
DECLARE @mastertable TABLE
([db_name]  NVARCHAR(MAX),
 [col_name] NVARCHAR(MAX)
);
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'select cast(''master'' as sysname) as db_name, name collate Latin1_General_CI_AI as col_name  from master.sys.tables ';
SELECT @sql = @sql+N' union all select '+QUOTENAME(name, '''')+', name collate Latin1_General_CI_AI as col_name from '+QUOTENAME(name)+N'.sys.tables'
FROM sys.databases
WHERE database_id > 1
      AND state = 0
      AND user_access = 0;
INSERT INTO @mastertable
EXEC sp_executesql
     @sql;




-- Declare variable table to hold database names
DECLARE @variablenametable TABLE
(Id           INT PRIMARY KEY IDENTITY(1, 1),
 variablename VARCHAR(255)
);
INSERT INTO @variablenametable(variablename)
       SELECT db_name
       FROM @mastertable
      -- where column is log
       WHERE col_name = 'log'
      -- where db like sp_
       and db_name like '%SP_%';

-- Holds the table name to stop
DECLARE @variablename VARCHAR(255); 

-- Dynamic SQL holder
DECLARE @variablenamekill NVARCHAR(MAX);
-- Holds the ID of the database in the loop
DECLARE @ID INT= 0;
-- Check if tables exist
IF EXISTS
(
    SELECT [db_name]
    FROM @mastertable
    WHERE [col_name] = 'log'
    and db_name like '%SP_%'
)
    BEGIN 
        -- Insert names of table into the table variable
        INSERT INTO @variablenametable(variablename)
               SELECT [db_name]
               FROM @mastertable
               WHERE [col_name] = 'log'
             and db_name like '%SP_%'; 

        -- Begin loop               
        WHILE(1 = 1)
            BEGIN   
                -- Grab first name of table to delete
                SELECT @variablename =
                (
                    SELECT TOP 1 variablename
                    FROM @variablenametable
                );  

                -- Sets dyanmic SQL to delete     
                SET @variablenamekill = N'TRUNCATE TABLE '+QUOTENAME(@variablename)+'.dbo.log;';

                -- delete tables
                EXEC sp_executesql
                     @variablenamekill;

                -- Removes table name from the list of tables
                DELETE FROM @variablenametable
                WHERE variablename = @variablename;

                -- No more tables, stops deleting
                IF
                (
                    SELECT COUNT(*)
                    FROM @variablenametable
                ) <= 0 BREAK;
                -- End Loop             
            END;
        -- End elseif
    END;
ELSE
PRINT 'All done';

I've edited my query based on work from Remus Rusanu on Stack Exchange. My original script would go through your choice of database and truncate all tables. The new script will go through your databases to match one like %SP_% and if it has a table called log. It will then sub in those databases and truncate the table log in each of those databases.

This is definitely not as performant as it was (I'm not saying the original was super polished either, but this is less so.), so I would recommend to groom the SQL a little if you need it to work better. I liked the other answer by Geoff Patterson as well.

  • Hi, I am trying to truncate table dbo.log which exist from over 100 databases weekly . also, I used a wild card from my query to only focus on DBs with the name SP_ in it. I think your scripts only works when used on a specific database. Thanks – Djoe1 Jun 20 '18 at 20:31
  • I'll be looking forward to hear from you on this when you're free. Appreciate. – Djoe1 Jun 20 '18 at 21:25
1

An alternative solution to dynamic TSQL is Powershell.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

#Sql Server Instance (Default instance in this example)
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "."

#Table to truncate
$TableToTruncate = 'dbo.TestTruncate'

#QueryToExecute
$QueryToExecute = "if OBJECT_ID('$TableToTruncate') is not null Truncate table $TableToTruncate"

#Loop through each database
foreach ($sqlDatabase in $sqlServer.databases) {
    Write-Host Looking at $sqlDatabase.name

    #Skip if system database
    if ($sqlDatabase.IsSystemObject -eq $false) {

        #Invoke the truncate command
        Invoke-Sqlcmd -ServerInstance $sqlserver.Name -Database $sqlDatabase.name -Query $QueryToExecute
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.