I'm currently trying to build a document for our project manager and one of the tasks is to find out which stored procedures use linked servers.- this is a one off job and not production code.
to avoid writing a cursor within a cursor i decided to go with the completely unsupported sp_msforeachdb
so, for each database query any object definition that has a recognised linked server (from sys.sysservers)
sp_msforeachdb 'SELECT o.name,''?'',xtype,s.srvname FROM ?.dbo.sysobjects o INNER JOIN master.sys.sysservers s ON OBJECT_DEFINITION(o.id) LIKE ''%''+s.srvname+''%'''
but i'm getting a few odd results - in particular a default constraint
ALTER TABLE [dbo].[InstantForum_TopicRatings] ADD DEFAULT ((0)) FOR [TopicID]
so, object_definition(id) for this constraint just returns "((0))" - which is a bit weird - I would have expected the object definition
but the query thinks that it is using a linked server (i'm changing the name for security) PGDb007
what have I done wrong ?