2

I am having difficulty in using a variable name for the database. The below script searches through all databases and finds database that match a certain condition. The matching databases are added to table #t. I then loop through each database by setting the @DB1 to each database in turn. The problem I'm getting is when it comes to using the database name in @DB1. Any idea how I can get each of my matching databases to update?

create table #t (
    ID int IDENTITY,DBName sysname not null
)
go
EXEC sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.SDE_server_config'') is not null insert into #t (DBName) select ''?'''



Declare @RowCount int
Declare @Counter int
Declare @DB1 varchar(max)

set @RowCount = (Select Count(*) from #t)
set @Counter=0


While @Counter <= @RowCount
Begin
select @Counter = @Counter + 1


set @DB1 =  = (select DBName FROM #t WHERE ID = @COUNTER)

:SETVAR DatabaseName @DB1



USE $(DatabaseName);

UPDATE dbo.SDE_server_config
        SET num_prop_value=0
        WHERE prop_name=CONNECTIONS;

End
  • Kim and Aaron Bertrand make a good and correct point below that sp_MSforeachDB is not supported and has some issues. It doesn't always operate over the entire list of databases. – msgisme Jul 13 '15 at 16:16
2

You dont really need a loop and there is no need to use the undocumented sp_MSforeachdb as there is a better alternative to it - written by Aaron Bertrand.

You can use below tsql - Run in SSMS by pressing CTRL+T:

set nocount on

select 'if OBJECT_ID(''dbo.SDE_server_config'') is not null'+char(10)+'begin '+char(10)+' UPDATE ' + quotename(name) + '.dbo.SDE_server_config SET num_prop_value=0
        WHERE prop_name=CONNECTIONS;' + char(10) + 'end '+char(10)+ 'else print ''there is nothing to update ...!;'''
from sys.databases
where database_id > 4
    and state_desc = 'ONLINE'

You can put above select statement in a variable and do and EXEC and save it in a .sql file and then use sqlcmd to just directly call that .sql file and run it on multiple servers.

1

Another approach: (different from @Kin's solution mostly in that you don't have to copy/paste the output or save to a .sql file):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'IF EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
  + N'.sys.objects WHERE name = N''SDE_server_config'')
  UPDATE ' + QUOTENAME(name) + '.dbo.SDE_server_config
    SET num_prop_value = 0 WHERE prop_name = ''CONNECTIONS'';'
FROM sys.databases;

EXEC sys.sp_executesql @sql;
0

The simplest option would be to add the update statement into the the string executed by sp_MSforeachDB:

EXEC sp_MSforeachdb 'use [?]; 
    if OBJECT_ID(''dbo.SDE_server_config'') is not null 
    update UPDATE dbo.SDE_server_config
    SET num_prop_value=0
    WHERE prop_name=CONNECTIONS;'

If you need to do something more complicated (not just an update statement), you can use dynamic sql:

--Put this with other variable declarations
DECLARE @SQL NVARCHAR(MAX)     

...

--Put this in WHILE loop
SET @SQL = 'USE ' + QUOTENAME(@DB1) + ' 
    update UPDATE dbo.SDE_server_config
    SET num_prop_value=0
    WHERE prop_name=CONNECTIONS;'
EXEC (@SQL);
  • Please note that sp_MSforeachdb is undocumented and not supported. – Kin Shah Jul 13 '15 at 13:36
  • And broken, too! – Aaron Bertrand Jul 13 '15 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.