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 need a script that will go to every db in server and if exsistes a table named "table1" will do

truncate table table1 
share|improve this question
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 Answer 1

up vote 2 down vote accepted

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 
share|improve this answer
2  
1) 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

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.