SQL with Manoj

Looks like a simple topic and some might wonder why another post as this information is already available in other forums and blogs. But here I’ll go ahead with the topic and talk about an issue that not many people are aware of.

There are times when you want to search a particular string or keyword in various database objects like Stored Procedures, Views, Functions, etc. What you do in that case?
#1. Go to object explorer, open up each object and search the string by scripting it out.
– OR –
#2. Query the system catalog tables or metadata views.

Option #1 is time consuming, slow and very cumbersome.
Option #2 is quick and easy in retrieving the information.

So, we will see in what all ways we can get the required information from our database & which is the best way.

As I said at the top of this post if you search online you will see lot of solutions, but all those solution have some minor hidden issues which we will see here.

We will check below how we can search the “[HumanResources].[Employee]” table getting used in what all objects. (Here we are not checking the dependency on objects, but just checking any text which can be an object, SQL reserved keyword or user specified string to build a SQL query or a T-SQL script.)

–> 1. The first way comes in people mind is to search with INFORMATION_SCHEMA.ROUTINES View:

USE [AdventureWorks2012]
GO

select ROUTINE_DEFINITION, * 
from INFORMATION_SCHEMA.ROUTINES 
where ROUTINE_DEFINITION like N'%[[]HumanResources].[[]Employee]%'

6 Records returned with following Stored Procedure names:

ROUTINE_NAME
ufnGetContactInformation
uspGetEmployeeManagers
uspGetManagerEmployees
uspUpdateEmployeeHireInfo
uspUpdateEmployeeLogin
uspUpdateEmployeePersonalInfo

–> 2. The second way is to search in sys.syscomments View:

select
	CASE 
		WHEN OBJECTPROPERTY(id, 'IsProcedure') = 1      THEN 'Stored Procedure'
		WHEN OBJECTPROPERTY(id, 'IsView') = 1			THEN 'View'
		WHEN OBJECTPROPERTY(id, 'IsScalarFunction') = 1 THEN 'Scalar Function'
		WHEN OBJECTPROPERTY(id, 'IsTableFunction') = 1  THEN 'Table Valued Function'
		WHEN OBJECTPROPERTY(id, 'IsInlineFunction') = 1 THEN 'Inline Function'
		WHEN OBJECTPROPERTY(id, 'IsTrigger') = 1        THEN 'Trigger'
	END AS ObjType, 
	* 
from sys.syscomments 
where text like N'%[[]HumanResources].[[]Employee]%'

This query returns 12 reocrds, with 1 Table Valued Function, 5 SPs, 1 Trigger & 5 Views.

–> 3. The third way is to search in sys.sql_modules View:

select
	CASE 
		WHEN OBJECTPROPERTY(object_id, 'IsProcedure') = 1      THEN 'Stored Procedure'
		WHEN OBJECTPROPERTY(object_id, 'IsView') = 1			THEN 'View'
		WHEN OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 THEN 'Scalar Function'
		WHEN OBJECTPROPERTY(object_id, 'IsTableFunction') = 1  THEN 'Table Valued Function'
		WHEN OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 THEN 'Inline Function'
		WHEN OBJECTPROPERTY(object_id, 'IsTrigger') = 1        THEN 'Trigger'
	END AS ObjType, 
	* 
from sys.sql_modules 
where definition like N'%[[]HumanResources].[[]Employee]%'

Same as above, returns 12 reocrds.

No issue till now, great!!!

Now, let’s check this scenario: I want to get all Database Objects containing the “[BikeFrame]” column name.

–> 1. When I choose to search with INFORMATION_SCHEMA.ROUTINES view it does not give any result:

select ROUTINE_DEFINITION, * 
from INFORMATION_SCHEMA.ROUTINES 
where ROUTINE_DEFINITION like N'%[[]BikeFrame]%'

Because this View stores only first 4000 characters in ROUTINE_DEFINITION column of NVARCHAR datatype. So the query gets truncated before the column name occurs.

–> 2. When I go with sys.syscomments view it also gives me nothing:

select * 
from sys.syscomments 
where text like N'%[[]BikeFrame]%'

Here this View stores the query-text in “text” column of NVARCHAR type with size of 4000 characters. The query also gets truncated here but it stores the query in chunks thus the rest part goes to the next rows.

So it should return the row with matching chunk from the matching row, but it does not, why? Because in this typical case the column name is getting splitted in different rows. Check by running this query:

select id, colid, ctext, text
from sys.syscomments 
where id = 2059154381

Output:-
id		colid	ctext			text
2059154381	1	0x0D000A00430052004...	CREATE VIEW [Production].[vProductModelCatalog...
2059154381	2	0x65004600720061006...	eFrame] ,[CatalogDescription].value(N'declare nam...

Here, you can see the second half text “eFrame]” of the column name is placed at beginning of the 2nd row.
And if you expand the “text” column you will see the first half text “[Bik” of the column name at the end of 1st row.

–> 3. When I go with sys.sql_modules view it gives me expected result:

select * 
from sys.sql_modules 
where definition like N'%[[]BikeFrame]%'

Because this View stores the query text in “definition” column of NVARHCAR(MAX) type with length of 2GB.

So, its better to use sys.sql_modules view to do all metadata based searches instead of INFORMATION_SCHEMA.ROUTINES & sys.syscomments views.