locked
How I need to run procedure in every database RRS feed

  • Question

  • How can I execute stored proc in every database?
    • Moved by Tom Phillips Friday, November 16, 2012 1:49 PM TSQL question (From:SQL Server Database Engine)
    Friday, November 16, 2012 10:48 AM

Answers

  • Hello Ray,

    with the undocumented & unsupported stored procedure sp_msforeachdb Example:

    EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'


    Olaf Helper

    Blog Xing

    • Proposed as answer by V. Keerthi Deep Friday, November 16, 2012 11:56 AM
    • Marked as answer by Ray Ben Friday, November 16, 2012 12:49 PM
    Friday, November 16, 2012 11:14 AM
  • To add to Olaf's response, you might want to enclose the database name in case you have database names that don't confirm to identifier naming rules (e.g. reserved keywords):

    EXECUTE sp_MSforeachdb 'USE [?]; PRINT DB_NAME();';

    Also, you can alternatively qualify your stored procedure name with the database name instead of the USE command:

    EXECUTE sp_MSforeachdb 'EXECUTE [?].dbo.usp_MyProc;';


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by V. Keerthi Deep Friday, November 16, 2012 11:56 AM
    • Marked as answer by Ray Ben Friday, November 16, 2012 12:48 PM
    Friday, November 16, 2012 11:54 AM
  • The other option is to create your stored procedure inside master database and mark it as an system object.

    sys.sp_MS_marksystemobject 

    Check out the link that shows how you can do this.

    http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Ray Ben Friday, November 16, 2012 12:48 PM
    Friday, November 16, 2012 12:44 PM
  • or this article: http://www.mssqltips.com/sqlservertip/1612/creating-your-own-sql-server-system-stored-procedures/


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Ray Ben Friday, November 16, 2012 12:48 PM
    Friday, November 16, 2012 12:45 PM

All replies

  • Hello Ray,

    with the undocumented & unsupported stored procedure sp_msforeachdb Example:

    EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'


    Olaf Helper

    Blog Xing

    • Proposed as answer by V. Keerthi Deep Friday, November 16, 2012 11:56 AM
    • Marked as answer by Ray Ben Friday, November 16, 2012 12:49 PM
    Friday, November 16, 2012 11:14 AM
  • To add to Olaf's response, you might want to enclose the database name in case you have database names that don't confirm to identifier naming rules (e.g. reserved keywords):

    EXECUTE sp_MSforeachdb 'USE [?]; PRINT DB_NAME();';

    Also, you can alternatively qualify your stored procedure name with the database name instead of the USE command:

    EXECUTE sp_MSforeachdb 'EXECUTE [?].dbo.usp_MyProc;';


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by V. Keerthi Deep Friday, November 16, 2012 11:56 AM
    • Marked as answer by Ray Ben Friday, November 16, 2012 12:48 PM
    Friday, November 16, 2012 11:54 AM
  • The other option is to create your stored procedure inside master database and mark it as an system object.

    sys.sp_MS_marksystemobject 

    Check out the link that shows how you can do this.

    http://weblogs.sqlteam.com/mladenp/archive/2007/01/18/58287.aspx


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Ray Ben Friday, November 16, 2012 12:48 PM
    Friday, November 16, 2012 12:44 PM
  • or this article: http://www.mssqltips.com/sqlservertip/1612/creating-your-own-sql-server-system-stored-procedures/


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Ray Ben Friday, November 16, 2012 12:48 PM
    Friday, November 16, 2012 12:45 PM