Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
    
What version first used sp_msforeachDB and sp_msforeachTable? –  JNK Aug 13 '12 at 14:14
    
@JNK That could be used going back at least to 2000. But it sucks, don't use it! –  Aaron Bertrand Aug 13 '12 at 14:15
1  
...and I searched for problems with sp_msforeachdb and got an Aaron Bertrand blog post as result #1. GO FIGURE! –  JNK Aug 13 '12 at 14:19
    
Yeah, the whole "meh, I don't think I'll deal with that database this time around" attitude that sp_msforeachdb seems to have is not very endearing. –  Aaron Bertrand Aug 13 '12 at 14:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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