Take the 2-minute tour ×

Any idea if it's possible to create a procedure in another database using T-SQL alone, where the name of the database is not known up front and has to be read from a table? Kind of like this example:

Use [MasterDatabase]
Declare @FirstDatabase nvarchar(100)
Select Top 1 @FirstDatabase=[ChildDatabase] From [ChildDatabases]
Declare @SQL nvarchar(4000)
Declare @CRLF nvarchar(10) Set @CRLF=nchar(13)+nchar(10)
Set @SQL =
    'Use [+'@Firstdatabase+']'+@CRLF+
    'Create Proc [Test] As Select 123'
Exec (@SQL)

See what I'm trying to do? This example fails because Go is actually not a T-SQL command but it something recognised by the query analyser/SQL management studio and produces an error. Remove the Go and it also fails because Create Proc must be the first line of the script. Arrgg!!

The syntax of T-SQL doesn't allow you do things like this:

Create [OtherDatabase].[dbo].[Test]

Which is a shame as it would work a treat! You can do that with Select statements, shame it's inconsistent:

Select * From [OtherDatabase]..[TheTable]

Cheers, Rob.

share|improve this question
What if you first change database with exec and then exec you storedproc? –  Richard L Jan 27 '09 at 15:25
I would do it like this as well: first send the part before "go", then (in a second SQL statement) send "create proc..." –  Christian Specht Jan 29 '09 at 13:15
add comment

4 Answers

up vote 0 down vote accepted

I dont think this can be done with TSQL.

You could use an SSIS package that looped the names and connected to the servers dynamically which creates the schema (procs ) you need.

This is probably what I would do as it means it is all contained within the package.

Configuration can be kept separate by either using a table or external xml file that contained the list of server/databases to deploy the schema to.

share|improve this answer
add comment

It's a pain, but this is what I do. I took this from an example I found on sqlteam, I think - you might have some quoting issues with the way I did the indiscriminate REPLACE:

DECLARE @sql AS varchar(MAX)
DECLARE @metasql as varchar(MAX)
DECLARE @PrintQuery AS bit
DECLARE @ExecQuery AS bit

SET @PrintQuery = 1
SET @ExecQuery = 0

SET @sql = 

SET @metasql = '
USE OtherDatabase
EXEC (''' + REPLACE(@sql, '''', '''''') + ''')

IF @PrintQuery = 1
    PRINT @metasql
IF @ExecQuery = 1
    EXEC (@metasql)
share|improve this answer
Hmm, yes I can see that working. An Exec within and Exec. Boy will the quoting get hairy! –  Rob Nicholson Jan 29 '09 at 11:56
add comment
declare @UseAndExecStatment nvarchar(4000),
        SQLString nvarchar(4000)

set @UseAndExecStatment = 'use ' + @DBName +' exec sp_executesql @SQLString'

set @SQLString = N'CREATE Procedure [Test] As Select 123'

exec sp_executesql  @UseAndExecStatment,
            N'@SQLString nvarchar(4000)', @SQLString=@SQLString
share|improve this answer
add comment

You could shell out to osql using xp_cmdshell, I suppose.

share|improve this answer
add comment

Your Answer


