sql server 2005 delete all table data in a set [DB].[audit].[tables]

Question

So I have a database with around 100 audit tables and I want to empty them out preferably in 1 sql query. The database has 2 sets of tables [audit] and [dbo]. I'm not even sure what I should be calling these groups of tables so finding any kind of results from google is proving difficult.

Any suggestions?

Answer 1

You can find all tables with a certain schema name like:

select name from sys.tables where schema_name(schema_id) = 'audit'

With a cursor, you iterate over those tables, and empty them using TRUNCATE TABLE:

use db
declare @query nvarchar(max)
declare @tablename nvarchar(max)
declare @curs cursor
set @curs = cursor for select name from sys.tables 
    where schema_name(schema_id) = 'audit'
open @curs
fetch next from @curs into @tablename
while @@FETCH_STATUS = 0
 begin
 set @query = N'truncate table audit.' + @tablename
 exec sp_executesql @query
 fetch next from @curs into @tablename
 end
close @curs
deallocate @curs

If you want to delete the tables instead, use:

set @query = N'drop table audit.' + @tablename

Answer 2

You could also use the sp_msforeachtable stored procedure. It lets you perform a query on each user table in the current DB.

For example the following will truncate all user tables in your DB

Use YourDB
Exec sp_msforeachtable 'TRUNCATE TABLE ?'

And this will truncate all user tables in the specified db that belong to the audit schema.

Use YourDB
Exec sp_msforeachtable @command1 = '
    if (Select Object_Schema_name(object_id(''?''))) = ''dbo''
    Begin 
        TRUNCATE TABLE ?
        print ''truncated '' + ''?'' 
    End
    '

Also Here is a blog entry with some more uses for this stored procedure.