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)