Post reply

Searching for obsolete server references in procs and functions.

  • August 14, 2012 at 12:13 pm

    #262877

    I found an article here on SSC containing the following sample script code. It looks fairly straight-forward, however when I plug in my search string of '[%SQL01%]' on the WHERE clause, it returns results to me that I cannot find the target search string in.

    Here's the sample code:

    SELECT *

    FROM msdb..syscomments

    WHERE text LIKE '%SIVApplicaton%'

    SELECT TOP 1000

    DB_NAME() AS DatabaseName,

    CASE WHEN OBJECTPROPERTY(object_id, 'IsProcedure') = 1 THEN 'Stored Procedure'

    WHEN OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 THEN 'Table Function'

    WHEN OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 THEN 'Scalar Function'

    WHEN OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 THEN 'Inline Function'

    ELSE NULL END AS ObjectType,

    OBJECT_SCHEMA_NAME(object_id) AS ObjectSchema,

    OBJECT_NAME(object_id) ObjectName,

    [definition] AS ObjectText, *

    FROM sys.sql_modules

    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1

    OR OBJECTPROPERTY(object_id, 'IsTableFunction') = 1

    OR OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1

    OR OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1

    AND [definition] LIKE N'%SQL01%'

    Does anyone have any ideas? SQL01 is an obsolete server name. It was aliased via a DNS entry so that [SQL01] actually points to [SQL05] in DNS. References to SQL01 therefore work, but we want to change all of these obsolete references so that we can bring up a new SQL Server named SQL01.

    It seems fairly trivial, but I am disturbed and peturbed that I'm getting erroneous results.

    Thanks in advance!

    Larry

  • August 14, 2012 at 1:21 pm

    #1525754

    In the WHERE clause you need to put parenthesis around all the OBJECTPROPERTY conditions.

    WHERE (OBJ()=1 OR OBJ()=1 OR ...) AND definition LIKE ...

    or even join the OBJECTPROPERTY function results with simple addition:

    WHERE OBJ() + OBJ() + OBJ() > 0 AND definition LIKE ...

  • August 14, 2012 at 1:57 pm

    #1525779

    You rock Mr. Coleman! Thank you!

    I looked that code over and over and never saw that those parens were missing! Sheesh!

    Many thanks for your help!

    Larry

Viewing 3 posts - 1 through 2 (of 2 total)

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