Executing on a different database.

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?

Although I always suggest not using dynamic SQL its the appropiate way to do some adhoc things:

DECLARE @.SQLText VARCHAR(MAX)
DECLARE @.DatabaseName VARCHAR(MAX)

SET @.DatabaseName = 'Mydatabase'
SET @.SQLTEXT = 'USE ' + @.DatabaseName

SELECT @.SQLTEXT = @.SQLTEXT + ' GO ' + ROUTINE_DEFINTION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE <YourFiltercriteriaHere>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql