Find Table in All Microsoft Dynamics GP Databases

I 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 (https://www.azurecurve.co.uk)
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(
	DBNAME VARCHAR(15)
	,TABLENAME VARCHAR(100)
)

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

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData