4

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

truncate table table1
eyalb
  • 203
  • 2
  • 10
  • 1
    Could you explain why you want to do this, as there may be alternatives that more useful. A simple truncate for each table will only work if you don't have foreign keys. It may be better to script your database and recreate for example. – Miles D Feb 13 '11 at 20:12

1 Answers1

4

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
bernd_k
  • 12,211
  • 23
  • 75
  • 111
  • 2
  • You should check only user databases. 2) Need to check for FKs, other way the truncate will fail. 3) This is a weapon in the right hands :-).
    • – Marian Feb 13 '11 at 14:55