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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
up vote 4 down vote accepted

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


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
    Set @sql = 'select * into #t1 from ' + @name + '..sysobjects where name = ''table1'' and type = ''U''' 
    set @rc = @@rowcount
    if @rc > 0 
        set @sql2 = 'use ' + @name + '; ' + 'Truncate table table1'
        print @sql2
    fetch next from db_cursor into @name

close db_cursor
deallocate db_cursor 
share|improve this answer
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


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.