March 27, 2013 at 8:50 am
Hi,
I would like do execute multiple statements in all databases.
I created the following code:
DECLARE
@C1_NAME VARCHAR(1000),
@SQL VARCHAR(1000)
DECLARE C1 CURSOR FOR
SELECT Name
FROM sys.databases
WHERE substring(name,1,3) = 'MyD'
ORDER BY NAME
OPEN C1
FETCH NEXT FROM C1 INTO @C1_NAME
WHILE @@FETCH_STATUS = '0'
BEGIN
SET @SQL = 'USE ' + @C1_NAME;
PRINT @SQL
EXECUTE( @SQL );
SET @SQL = 'SELECT * FROM MyTable'; -- or update MyTable set field1 = 1;
PRINT @SQL
EXECUTE( @SQL );
FETCH NEXT FROM C1 INTO @C1_NAME
END
CLOSE C1
DEALLOCATE C1
GO
The problem is that it is running only on the database where he was processed this instruction...
There is another way to pass all database and execute SQL commands on each base?
Thanks!!
Jose Anchieta Carvalho Junior
March 27, 2013 at 8:54 am
Or you could just do
SET @SQL = 'SELECT * FROM '+QuoteName(@C1_Name) + '..[MyTable]'
PRINT @SQL
EXEC (@SQL)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 27, 2013 at 9:01 am
The use statement gets its own context in the exec() call. It doesn't change the context for the calling program. You can do what was suggested above by Jason (recommended) or do this:
SET @SQL = 'USE ' + @C1_NAME + '; SELECT * FROM MyTable'; -- or update MyTable set field1 = 1;';
PRINT @SQL
EXECUTE( @SQL );
March 27, 2013 at 9:01 am
JoseACJr (3/27/2013)
Hi,I would like do execute multiple statements in all databases.
I created the following code:
DECLARE
@C1_NAME VARCHAR(1000),
@SQL VARCHAR(1000)
DECLARE C1 CURSOR FOR
SELECT Name
FROM sys.databases
WHERE substring(name,1,3) = 'MyD'
ORDER BY NAME
OPEN C1
FETCH NEXT FROM C1 INTO @C1_NAME
WHILE @@FETCH_STATUS = '0'
BEGIN
SET @SQL = 'USE ' + @C1_NAME;
PRINT @SQL
EXECUTE( @SQL );
SET @SQL = 'SELECT * FROM MyTable'; -- or update MyTable set field1 = 1;
PRINT @SQL
EXECUTE( @SQL );
FETCH NEXT FROM C1 INTO @C1_NAME
END
CLOSE C1
DEALLOCATE C1
GO
The problem is that it is running only on the database where he was processed this instruction...
There is another way to pass all database and execute SQL commands on each base?
Thanks!!
Jose Anchieta Carvalho Junior
For future reference the reason it's executing only in the one database is that you have "Use Database;" executed separately from your other dynamic statement. What happens is that it executes the use database command, reverts to the current database and then executes the select statement. Your dynamic statement would need to look more like:
SET @SQL = 'USE ' + @C1_NAME + ';SELECT * FROM MyTable'
in order to be used against the selected database.
Edited: Dang, Steve beat me to it.
March 27, 2013 at 9:27 am
Or, another option using smoke and mirrors inside the WHILE LOOP:
set @SQL = 'create synonym MyTarget for ' + @C1_NAME + '.dbo.MyTable';
exec (@SQL);
select * from MyTarget
drop synonym MyTarget;
March 27, 2013 at 10:55 am
perhaps use EXEC sp_MSforeachdb @command1='use ?; SELECT * FROM mytable' ?
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 27, 2013 at 11:11 am
EXEC sp_MSforeachdb '
IF NOT EXISTS(SELECT 1 FROM [?].sys.tables WHERE name = N''myTable'')
RETURN
SELECT ''?'' AS Db_Name, *
FROM [?]..mytable
'
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".
March 27, 2013 at 2:44 pm
Thanks to all
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply