Overview: Usage of an Information Schema view to find text within a SQL Server 2008 R2 stored procedure, function, table, or view.
Deprecated System Tables
So, you’re used to querying SysObjects, SysColumns, SysComments? Me too! Bad news is the System Tables are deprecated & not supported in the next version of SQL Server: Deprecated Database Engine Features in SQL Server 2008 R2. Good news is we have very good alternatives.
As a BI Developer, I mostly used those system tables to search for text within a stored procedure, text in a column name, and so on. Of course DBAs and developers have a never-ending list of good uses for this metadata.
Alternatives to the System Tables
According to MSDN, there’s 5 ways to gain access to system metadata:
- Catalog views (recommended)
- Information schema views
- OLE DB schema rowsets
- ODBC catalog functions
- System stored procedures and functions
For my simple needs, I’ve turned to the catalog views and the information schema views.
Searching for Text Within a Stored Procedure or Function
The ***OLD*** way I’d have done this was (replace BusinessEntityID with the string you are searching for):
USE AdventureWorks2008R2
GO
DECLARE @Keyword varchar(30)
SET @Keyword ='%'+'BusinessEntityID'+'%'
SELECTDISTINCT
SysObj.Name
,SysObj.[Type]
,SysObj.refdate AS'LastAltered'
FROMsysobjectsAS SysObj WITH (NOLOCK)
INNERJOINsyscommentsAS SysComm WITH (NOLOCK)
ON SysObj.Id = SysComm.ID
AND SysObj.[Type] IN('P','TF','IF','FN')--Proc or Function
AND SysComm.[Text] LIKE @Keyword
ORDERBY SysObj.Name
The ***NEW*** way I’m doing this now is:
USE AdventureWorks2008R2
GO
DECLARE @Keyword varchar(30)
SET @Keyword ='%'+'BusinessEntityID'+'%'
SELECT
SPECIFIC_NAME
,ROUTINE_TYPE
,LAST_ALTERED
FROMinformation_schema.routines
WHERE ROUTINE_DEFINITION LIKE @Keyword
The reason I like using the Information Schema for this purpose is because the entire stored procedure text is presented within one field when you query the routines view.
See the following example where I retrieve the 'create procedure' syntax for one stored proc:
USE AdventureWorks2008R2
GO
EXEC
sp_helptext'uspGetManagerEmployees'
We get the results from the previous query returned in rows. While the catalog views like sys.objects, sys.columns, sys.procedures are certainly very helpful, I've not yet found a replacement for the deprecated syscomments table. Therefore, I'm using an Information Schema view, rather than the Catalog views, when I want to search for text.