Parameterizing database name in update query

Question

Parameterizing database name in update query

stricknyn

SSCertifiable

Points: 6560
More actions
April 21, 2020 at 6:30 pm

#3745095
Hi everyone,
Seems like this should be simple, but haven't found a way to accomplish what I want to do. So I would like to parameterize the database name in an update statement. I know can accomplish this easily using dynamic SQL, but I do not want to use that for obvious reasons.
Basically, I have to update numerous identical DBs (fyi, same tables in all of the databases) and I'd like to do it without resorting to dynamic SQL. So something like:
```
update MyDatabaseParameterHere.dbo.Table1
set MyField = 'SomeStuff'
where MyField = 'MyValue'
```
Simplistic example above. I want the database to be parameterized which will allow me to run this across multiple databases. Any pointers would be greatly appreciated!
Thanks!
Strick

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

One option is using SQLCMD mode

USE DB1;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB2;
:r C:\SqlScript\YourLargeScript.sql
GO
USE DB3;
:r C:\SqlScript\YourLargeScript.sql
GO

(Example stolen from here.)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

stricknyn SSCertifiable Points: 6560 More actions · Answer 2

Thanks for your response Phil. This would work, but I was attempting for it to be more dynamic. Needed to be able to feed a parameter becasue there are a crap load of DBs to update and it won't be the same DBs each time either. So needed to be able to feed the database into the parameter and it updates the table in that DB.

Strick

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 3

stricknyn wrote:

Thanks for your response Phil. This would work, but I was attempting for it to be more dynamic. Needed to be able to feed a parameter becasue there are a crap load of DBs to update and it won't be the same DBs each time either. So needed to be able to feed the database into the parameter and it updates the table in that DB.
Strick

You could do it with dynamic SQL.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

You could move it out of SSMS and use Powershell - a simple loop over a list of databases to apply the updates:

$servers = ("server1","server2","server3");
$databases = ("db1","db2","db3");

$servers | % {
    $server = $_;
    $databases | % {
        $database = $_;
        Invoke-SqlCmd -ServerInstance $server -Database $database -Inputfile "script file";
        }
    }

You could even expand on this type of solution - building a PS script that accepts a server parameter and a database parameter then loop while calling the individual scripts(s) with the appropriate parameters.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101091 More actions · Answer 5

Create a proc in the master db, with a name beginning with sp_, and you can directly run it against any user db. [If you're in Azure, nevermind, this won't work there.]

First this:

USE master;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE PROCEDURE dbo.sp_update_common_table
    @new_column_value varchar(8000) = 'SomeStuff',
    @where_value varchar(8000) = 'MyValue'
AS
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NOCOUNT ON; 
SET NUMERIC_ROUNDABORT OFF;
SET XACT_ABORT ON;

UPDATE dbo.Table1
SET MyField = @new_column_value
WHERE MyField = @where_value
/*end of proc*/
GO
EXEC sp_MS_marksystemobject 'dbo.sp_update_common_table'
GO

Then this:

EXEC userdb1.dbo.sp_update_common_table

EXEC userdb2.dbo.sp_update_common_table

EXEC userdb3.dbo.sp_update_common_table

etc.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

You could create a synonym that points to a database, run your code, drop the synonym, recreate it pointing to another database, run the code, wash-rinse-repeat. It's dynamic without having to write the "big" code as dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)