Errors in searching for linked servers in procs

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    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

  • rVadim

    Hall of Fame

    Points: 3937

    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.

  • GonnaCatchIT

    SSCrazy

    Points: 2795

    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..

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21197

    ok - the issue is why is ((0)) matching %PGDb007%

    MVDBA

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply