truncate table for all db in sql server 2000

Question

i need a script that will go to every db in server and if exsistes a table named "table1" will do

truncate table table1 

Answer 1

First run this script to see which tables would be truncated. Then do the obvious modification.

SET NOCOUNT ON

declare @name sysname
declare @sql varchar(255)
declare @sql2 varchar(255)

declare @rc int
declare db_cursor cursor for

select name from master..sysdatabases
open db_cursor

fetch next from db_cursor into @name
while @@FETCH_STATUS = 0
begin 
    Set @sql = 'select * into #t1 from ' + @name + '..sysobjects where name = ''table1'' and type = ''U''' 
    EXEC(@sql)
    set @rc = @@rowcount
    if @rc > 0 
    begin
        set @sql2 = 'use ' + @name + '; ' + 'Truncate table table1'
        print @sql2
        --Exec(@sql2)
    end
    fetch next from db_cursor into @name
end

close db_cursor
deallocate db_cursor