Running SQL commands against all GP Company Databases

David Meego

Last week, I posted the article below explaining how you can use the osql.exe command to run a script against all Microsoft Dynamics GP company databases. The method described in this post is best if you have a *.sql script file to execute against each company database:

Running a SQL Script against all GP Company Databases

I have seen some discussions on the newsgroups about the sp_MSforeachdb system stored procedure and so decided to elaborate on this subject.

If you are already logged into SQL Server Query Analyzer or SQL Server Management Studio, you can take advantage of the sp_MSforeachdb system stored procedure. This stored procedure can be used to run a series of SQL commands against every database contained in the current SQL Server instance. However, this will include non Microsoft Dynamics GP company databases as well.

The following example code (provided by Robert Cavill) can be used to check that the current database is actually a Microsoft Dynamics GP company database before executing the commands against it.

Note: As the entire script is contained in quotes, you will need to change any single quotes in your commands to two single quotes. Make sure you include the closing single quote after the end statement.

exec sp_MSforeachdb
' use ?
if exists ( select INTERID from DYNAMICS..SY01500 D where INTERID = ''?'' )
begin
    /* this ia a Great Plains company db */
    print ''Processing database ?''
    /* Insert your script below this Line, Note: Double up single quotes */

    /* Insert your script above this Line, Note: Double up single quotes */
end
'

Yet another method which uses a SQL cursor to run through all the companies is shown in Mariano Gomez's post here.

I hope you find this script useful.

David