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
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