Run operations on all the tables in all the databases

Question

I'm trying to create a SQL Server script that applies some operations to all the tables in all the databases. I need to rename some tables if some conditions are respected, truncate the tables otherwise.

This is my script

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ''
IF(substring(&, 1, 3)=pv_ and right(&, 5) != _data and right(&, 4) != _BCK)
       exec sp_RENAME & , &_BCK''
ELSE IF (right(&, 4) != _BCK)
       TRUNCATE  TABLE &
@replacechar = ''&'''

I got some errors but I'm new to SQL Server and I have not idea how to fix this script.

Any suggestions?

Many thanks

score 3 · Accepted Answer · answered Aug 24 '12 at 06:20

Here is a solution for start. It won't be quick, but it loops all tables of all databases on the server. Inside in the second cursor you can deceide what to do with the table.

(The query is not optimalized, just a quick solution)

DECLARE @DBName NVARCHAR(50)
DECLARE @TableName NVARCHAR(100)
DECLARE @DynamicSQL NVARCHAR(300)

DECLARE @DBCursor CURSOR
SET @DBCursor = CURSOR FOR
 SELECT NAME FROM SYS.DATABASES
 WHERE NAME NOT IN ('master','tempdb','model','msdb')

OPEN @DBCursor
FETCH NEXT FROM @DBCursor INTO @DBName

WHILE @@FETCH_STATUS = 0
 BEGIN

 CREATE TABLE #TempTableDatas
 (
 name varchar(100),
 objectID int
 )

  SET @DynamicSQL = 'INSERT INTO #TempTableDatas
  SELECT name, object_id FROM [' + @DBName + ']' + '.sys.Tables '
  EXEC SP_EXECUTESQL @DynamicSQL

    DECLARE @TableCursor CURSOR
    SET @TableCursor = CURSOR FOR
     SELECT name FROM #TempTableDatas

      OPEN @TableCursor
      FETCH NEXT FROM @TableCursor INTO @TableName
      WHILE @@FETCH_STATUS = 0
      BEGIN

        SELECT @TableName, @DBName

       FETCH NEXT FROM @TableCursor INTO @TableName
      END

    CLOSE @TableCursor
    DEALLOCATE @TableCursor 
    DROP TABLE #TempTableDatas
  FETCH NEXT FROM @DBCursor INTO @DBName
 END

CLOSE @DBCursor
DEALLOCATE @DBCursor

Run operations on all the tables in all the databases

1 Answers1