False positives and other incorrect results

A free Management Studio add-in to search for SQL in your databases

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, Adam, justin.caldicott

False positives and other incorrect results

Postby blueneutron » Thu Jan 20, 2011 11:36 am

I'm seemingly getting false positives and other incorrect results when using SQL Search SQL Search 1.0.0.321. I'm using it with SSMS 10.0.2531.0 to search a SQL Server 2005 database. OS is Windows 7 32-bit edition (6.1.7600).

Example: search term vw_ec_get1 results include object vw_ec_get2010 supposedly with a match on Text. View vw_ec_get2010 does not contain text vw_ec_get1, and the Detail incorrectly shows the definition for vw_ec_get1 i.e. "CREATE VIEW [dbo].[vw_ec_get1] AS...". A search of vw_ec_get2010 also returns 2 lines, both with Detail "CREATE VIEW [dbo].[vw_ec_get1] AS...". In both cases, Exact Match is ticked, and I've tried the Force Reindex option. Double clicking on each result line for both searches selects the correct object in Object Explorer.

Any ideas?
blueneutron
 
Posts: 2
Joined: Thu Jan 20, 2011 11:14 am

Postby blueneutron » Thu Jan 20, 2011 1:03 pm

Believe I've found the cause. View vw_ec_get1 had been renamed to vw_ec_get2010. Under the covers, when you rename a VIEW (PROCEDURE too?), the original text in syscomments is not updated. This was verified using this query:
Code: Select all
SELECT OBJECT_NAME(id), text, * FROM syscomments WHERE TEXT LIKE '%vw_ec_get1%'

The text for both starts
CREATE VIEW [dbo].[vw_ec_get1]

Some other magic comes into play when you use a renamed query as it still works.

It would be nice if SQL Search could be updated to accommodate this, though the built-in information_schema views don't seem to in SQL 2005.
blueneutron
 
Posts: 2
Joined: Thu Jan 20, 2011 11:14 am


Return to SQL Search 1

Who is online

Users browsing this forum: No registered users and 0 guests