September 13, 2019 at 12:53 pm
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 ?
MVDBA
September 13, 2019 at 6:38 pm
When I'm looking for text in procedures I usually query sys.sql_modules view, something like this:
SELECT
o.object_id
,SchemaName = s.name
,ObjectName = o.name
,o.[type]
,m.[definition]
FROM sys.objects o
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE o.[type] IN ('P','V','FN','TF')
AND o.is_ms_shipped = 0
AND m.[definition] LIKE '%PGDb007%'
--Vadim R.
September 17, 2019 at 3:53 am
If ONLY procedures are in scope, you can use this
--Find a string in SPC
select * from sys.procedures where object_definition(object_id) like '%string%'
Run it in each of the DB's manually or automate with some cursor or by wrapping it in a spc..
September 17, 2019 at 8:02 am
ok - the issue is why is ((0)) matching %PGDb007%
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply