Find Table in All Microsoft Dynamics GP Databases

Microsoft Dynamics GPI was doing some work with a client recently with a custom extension being tested in some Microsoft Dynamics GP companies. Due to how it is deployed (very manually) we had only deployed it to some databases and not all.

Due to other project commitments, no testing was done for a while and when we returned, we weren’t sure if the deployment notes listed all databases correctly or if some of those databases had been overwritten for other testing.

So, a small script was needed to check for the presence of a custom table in all databases; the below is what I came up with to check for a table in all Microsoft Dynamics GP databases:

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
DECLARE @command nvarchar(max)

DECLARE @SystemDatabase VARCHAR(15) = 'DYNAMICS'
DECLARE @Table VARCHAR(50) = 'SY00800'
CREATE TABLE #ReturnedData(

SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = WHERE = ''?'' AND ( = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL))
							USE [?];
							INSERT INTO #ReturnedData (dbname, tablename) (SELECT DB_NAME() AS ''DB_NAME'', FROM sys.objects AS o WHERE LIKE ''' + @Table + '%'')

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData

Can ISC Software help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.

Your Name

Your Email


Your Enquiry

What should we write about next?

Your Name

Your Email

Suggested Topic

Suggestion Details

1 thought on “Find Table in All Microsoft Dynamics GP Databases

  1. Pingback: Comment

Leave a Reply

Your email address will not be published. Required fields are marked *