September 13, 2007 at 12:25 am
Hi all
I want to execute an SP to all the databases.
But i don't have the SP in all of them.
Please help me.
Nithyapriya
R
September 13, 2007 at 1:27 am
You may want to call you stored procedure sp_something. If it starts with 'sp_' and is in the master database, it will be available in all of your databases.
For executing them either iterate through the databases using a cursor, or use the sp_foreachdb (which is also using a cursor, not documented in Books Online, but there is plenty of information on this site too)
Regards,
Andras
September 13, 2007 at 4:56 am
For executing the stored procedure using a cursor, you can use this one:
-- Var Declare
DECLARE @name varchar(100)
DECLARE @Database varchar(100)
DECLARE @query nvarchar(4000)
-- Cursor Declare
DECLARE DB CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
where name <> 'tempdb'
and status <> 4194841
order by name
-- Cursor start
OPEN DB
FETCH NEXT FROM DB INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Exec sp
SET @Database = @name
SET @query =
'
-- insert your sp
'
EXEC sp_executesql @query
-- Cursor upgrade
FETCH NEXT FROM DB INTO @name
END
-- Cursor end
CLOSE DB
DEALLOCATE DB
September 13, 2007 at 9:59 am
Thanks for your information.
I got it worked.
Regards,
R
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply