SQL Server Searching Object Text

I had a situation where soft deletes were going to be implemented in the database, but for the past year hard deleted were being implemented.   On every table the IsDeleted column was created.  The newer Stored Procedures, Views, and Functions included IsDeleted in there WHERE statement, but all the db object that were created prior to implementing the IsDeleted needed to be changed to include the IsDeleted. 
 
The following was my first attempt to identify DB objects that did not include IsDeleted.  This seemed to return the correct data. But, there seem to be a problem.  There were situations where the results that were returned included duplicate names.  Come to find out the [Text] column that is returned from syscomments is NVARCHAR(4000).  If the [Text] of DB object exceeded 4000 characters an additional records was created every additional 4000 characters.   There were a few stored procedures that surpassed 4000 characters and in-turn had duplicate Object_names returned in the results .  This is not that big of a deal, but I wasn’t getting back the results I expected. 
 

SELECT OBJECT_NAME(id), *

FROM syscomments

WHERE

(

OBJECTPROPERTY(id, ‘IsScalarFunction’) = 1

OR OBJECTPROPERTY(id, ‘IsTableFunction’) = 1

OR OBJECTPROPERTY(id, ‘IsProcedure’) = 1

OR OBJECTPROPERTY(id, ‘IsView’) = 1

)

AND [Text] NOT LIKE ‘%IsDeleted%’

 
I found a solution to my problem by referencing sys.objects table and using the function OBJECT_DEFINITION, which returns the Transact-SQL source text of the definition of a specified object.  OBJECT_DEFINITION is not limited to NVARCHAR(4000), but instead is NVARCHAR(MAX).  I probably could have referenced INFORMATION_SCHEMA.Tables, but decided not to mess with it today and just wanted to get it to work.
 

SELECT

Name, *

FROM

sys.objects

WHERE

(

  OBJECTPROPERTY(object_id, ‘IsScalarFunction’) = 1

  OR OBJECTPROPERTY(object_id, ‘IsTableFunction’) = 1

  OR OBJECTPROPERTY(object_id, ‘IsProcedure’) = 1

  OR OBJECTPROPERTY(object_id, ‘IsView’) = 1

)

AND OBJECT_DEFINITION(object_id) NOT LIKE ‘%IsDeleted%’

 
 
 
References:
SQL Server OBJECT_DEFINITION (Transact-SQL)
 

SQL Server sysobjects

 

Searching Syscomments Accurately (View the Comments section)

 

Leave a Reply

Your email address will not be published. Required fields are marked *