Exec SP to all the database in a server

• Prakash-205770

  Ten Centuries

  Points: 1079

  More actions

  September 13, 2007 at 12:25 am

  #177164

  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

  ---

  Kindest Regards,

  R

• Andras Belokosztolszki

  SSC-Insane

  Points: 22031

  More actions

  September 13, 2007 at 1:27 am

  #733386

  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

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Fracn

  Old Hand

  Points: 389

  More actions

  September 13, 2007 at 4:56 am

  #733427

  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

• Prakash-205770

  Ten Centuries

  Points: 1079

  More actions

  September 13, 2007 at 9:59 am

  #733523

  Thanks for your information.

  I got it worked.

  Regards,

   

  ---

  Kindest Regards,

  R