How to truncate all tables with a specific name in all databases

Question

I want to delete all tables named error_log in all of my databases that have such a table.

truncate table error_log

I am using SQL Server 2000.

Answer 1

In SQL Server 2000 you can do this using a cursor against sysdatabases and building a check against each db's sysobjects table for the error_log object. If it exists, truncate. Rinse & repeat.

DECLARE @sql NVARCHAR(4000), @db SYSNAME;

DECLARE c CURSOR
  LOCAL READ_ONLY FORWARD_ONLY STATIC
  FOR SELECT name FROM sysdatabases WHERE dbid > 4
  AND CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'Status')) = 'ONLINE';

OPEN c;

FETCH NEXT FROM c INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(@db) +'.dbo.sysobjects 
    WHERE name = ''error_log'' AND xtype=''U'')
    TRUNCATE TABLE ' + QUOTENAME(@db) + '.dbo.error_log;';

  EXEC sp_executesql @sql;

  FETCH NEXT FROM c INTO @db;
END

CLOSE c; DEALLOCATE c;

In SQL Server 2005 and up, you can do this without the explicit cursor (and using the more modern catalog views), but in 2000 this method would not be safe - depending on how many databases you have you would risk exceeding 4000 characters, and you can't use a MAX type in SQL Server 2000 (or declare TEXT/NTEXT as a local variable):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + N'
  IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
  +'.sys.tables WHERE name = ''error_log'')
      TRUNCATE TABLE ' + QUOTENAME(name) + '.dbo.error_log;'
  FROM sys.databases 
  WHERE database_id > 4
  AND state_desc = 'ONLINE';

EXEC sp_executesql @sql;