locked
Reg:SQL Script that has to run in multiple databases by using SQLCMD ... RRS feed

  • Question

  • HI Experts,

    in my production server i have nearly 20 databases.i want to run one sql script in all existing databases .

    How can i achieve this by using batch file that uses the SQLCMD keyword.

    Thanks

    Friday, September 21, 2018 10:36 AM

All replies

  • HI Experts,

    in my production server i have nearly 20 databases.i want to run one sql script in all existing databases .

    How can i achieve this by using batch file that uses the SQLCMD keyword.

    Thanks

    HI Ramesh 

    you can use msforeachdb to execute script on all the database.



    Ramesh Babu Vavilla MCTS,MSBI

    Friday, September 21, 2018 10:59 AM
  • Hi babu,

    Thanq for your reply..

    i want to eleminate some of the databases like system databases and some of the user databases ..

    how can u achive this by using msforeachdb ?

    Thanks in Advance.

    Friday, September 21, 2018 12:33 PM
  • Hi babu,

    Thanq for your reply..

    i want to eleminate some of the databases like system databases and some of the user databases ..

    how can u achive this by using msforeachdb ?

    Thanks in Advance.

    by including a check inside sp_Msforeachdb

    like

    EXEC sp_Msforeachdb ' IF ''?'' NOT IN (''msdb'',''master'',''model'',''tempdb'')
    <your code>'

    In case you have Report Server installed and need to exclude its db as well make sure you add it too. Similarly SSISDB for integration services catalog

    EXEC sp_Msforeachdb ' IF ''?'' NOT IN (''msdb'',''master'',''model'',''tempdb'',''ReportServer<version>'',''ReportServer<Version>TempDB'',''SSISDB'')
    <your code>'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, September 21, 2018 1:37 PM
  • Hi Vikas,

    Thanks for your reply.

    i need to run the stored procedure having dynamic sql (alredy having single quotes) to required databases .

    so please suggest the solution as there may be quotes issue in sql server stored procedure.

    Thanks.

    Friday, September 21, 2018 2:33 PM