August 14, 2012 at 12:13 pm
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
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
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