In preparation for a migration project we are trying to identify any queries that select data across databases.
This can be of the form:
SELECT fieldA, fieldB FROM dbo.TableA a JOIN DatabaseB.dbo.TableB b ON b.ID = a.ID
DECLARE @resultFromDatabaseB VARCHAR(50) SELECT @resultFromDatabaseB = b.ID FROM DatabaseB.dbo.TableB SELECT fieldA, fieldB FROM dbo.TableA a WHERE a.ID = @resultFromDatabaseB
or any other variation (including INSERTS/UPDATES/etc...)
Unfortunately the majority of queries are not stored procedures so I cannot do static analysis of the SQL text, my current plan calls for recording the cached query text and doing static analysis on them.
Are there any alternatives to this approach? ie: use of extended events/trace/audit?