Change DB for Exec
-
I have an sp that I use to update all DB's. I supply it a block of T-SQL and it executes it using exec(@TSQL).
I want to the TSQL to execute in a given database. I cannot implement 'USE dbname' in my script, as the script may contain a 'Create Proc' or 'Drop' command/s.
How can I get my sp to change database and execute the script correctly with 'create' and 'drop'??
Any help would be appreciated.
-
Try this...
Create Table [dbname]..[TableName]
example:
From the master db I would use:
Create Table MyDatabase..MyTable
(
MyColumn1 Int,
MyColumn2 Float
)
To create a table in MyDatabase
-
What terryute shows should work, but you will have to use dynamic sql to do.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
C'mon guys. You can do better than that. Perhaps you misunderstood? I'll rephrase.
If I use a script for making changes to all db's (using cursor of sysdatabases), the script includes exec('some TSQL') to execute some T-SQL on each db. The script is running from Master, but executing the T-SQL on each database one by one.
This works for simple T-SQL where I can include a reference to the database (e.g. create table [dbname]..[tablename]).
If the script includes dropping and creating a SP (for example), I cannot include [dbname] in the T-SQL (qualified db ref not supported).
Also cannot implement 'use [dbname]' (as drop or create must be first commands executed).
How can I change the target database for my exec('some TSQL') command? Or can someone offer a better approach?
Thanks heaps.
-
You could try something like this.
EXEC sp_MSForEachDB '
If ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')
BEGIN
USE ? /* Note put ? inside [] to handle odd characters, forum code kept change to
. */DECLARE @SQLStr VARCHAR(5000)
/* Put your code inside here. */
SET @SQLStr = ''
CREATE PROCEDURE ip_Test AS
SELECT * FROM t1
''
EXEC (@SQLStr)
END'
Edited by - antares686 on 04/04/2002 05:51:22 AM
. */Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply