sql-serversp-msforeachdb

Run operations on all the tables in all the databases


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


Solution

  • 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