September 29, 2009 at 8:47 am
Hello, I am trying to figure out an easy way to execute a single sql script against multiple database via Management Studio.
My example below doesn't work, but in theory (based on pouring through several forums) it's what I'm trying to do. Can it be done this way?
EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
sqlcmd -i C:\execute.sql
Any help is appreciated. If it can't be done easily via Management Studio, I'd be open to other suggestions too.
thanks,
Mike
September 29, 2009 at 8:56 am
Hi Mike, there is no "Easy" way of doing this through SSMS, but here's what I've recently seen in use on a production environment:
You can use the SQLCmd mode (Query -> SQLCMD Mode) in SSMS, and use a piece of script like this one.
:Setvar MainServer "YourServerName"
:Setvar MainDB "YourDBName"
:setvar MainTable1 "Table1"
:setvar MainTable2 "Table2"
:connect $(MainServer) -U USER -P Password
GO
use $(MainDB)
Go
select * from $(MainTable1)
Select * from $(MainTable2)
Go
This is a small example, but it can permit you to load a file, and execute it. Look it up on BOL, and you can get a lot of examples to actually load a file, and execute it on multiple servers.
Hope this helps,
Cheers,
J-F
September 29, 2009 at 9:43 am
This is what cursors were made for. This is a basic gist because I never remember the exact cursor syntax off the top of my head. (And make it a point not to)
DECLARE DBCursor CURSOR FOR
SELECT '['+ name + ']'
FROM sys.databases
WHERE name not in ('master','msdb','tempdb','model')
select next from dbcursor into @name
while...
SET @sql = 'USE ' + @name
SET @sql = @sql + ' exec yourscript'
EXEC(@SQL)
fetch next..
end cursor
deallocate cursor
September 29, 2009 at 11:58 am
I can get the cursor running with just a quick select statement, but is there a way I can have it execute a file?
This works for the sql stmt to show me db name and a count
DECLARE @name varchar(100)
DECLARE @sql varchar (4000)
DECLARE DBCursor CURSOR FOR
SELECT '['+ name + ']'
FROM sys.databases
WHERE name not in ('master','msdb','tempdb','model')
OPEN DBCursor
fetch next from DBCursor into @name
while @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @name
SET @sql = @sql + ''',count(*) from tbldocuments'
EXEC(@SQL)
fetch next from DBCursor into @name
end
CLOSE DBCursor
deallocate DBCursor
But I am trying something like this, and it fails:
DECLARE @name varchar(100)
DECLARE @sql varchar (4000)
DECLARE DBCursor CURSOR FOR
SELECT '['+ name + ']'
FROM sys.databases
WHERE name not in ('master','msdb','tempdb','model')
OPEN DBCursor
fetch next from DBCursor into @name
while @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @name
SET @sql = @sql + 'exec c:\test.sql'
EXEC(@SQL)
fetch next from DBCursor into @name
end
CLOSE DBCursor
deallocate DBCursor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply