programmatically retrieve SQL Server stored procedure source code, query wheter the code contains some keyword.

Here is a quick script to receptive the SQL Server sp source code, Or query whether it contains some keyword or Not. the keyword could be a table name, snippet. etc.

create table #test1( text varchar(2000)) create table #test2( text varchar(2000)) insert into #test1 select SCHEMA_NAME(schema_id) + '.'+name from sys.procedures DECLARE db_cursor CURSOR FOR  select text from #test1 declare @name varchar(3000) OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name   WHILE @@FETCH_STATUS = 0   BEGIN          insert into #test2   exec sp_HelpText @name        if exists(select * from #test2 where  text like '%yourkeyword%')         print 'found,procedure is '  + @name         FETCH NEXT FROM db_cursor INTO @name   END   CLOSE db_cursor   DEALLOCATE db_cursor drop table #test1 drop table #test2

If you query all adventoreworks sp for AS keyword. you will get

(11 row(s) affected) found,procedure is dbo.uspPrintError (43 row(s) affected) found,procedure is dbo.uspLogError (31 row(s) affected) found,procedure is dbo.uspGetBillOfMaterials (30 row(s) affected) found,procedure is dbo.uspGetEmployeeManagers (30 row(s) affected) found,procedure is dbo.uspGetManagerEmployees (31 row(s) affected) found,procedure is dbo.uspGetWhereUsedProductID (35 row(s) affected) found,procedure is HumanResources.uspUpdateEmployeeHireInfo (24 row(s) affected) found,procedure is HumanResources.uspUpdateEmployeeLogin (22 row(s) affected) found,procedure is HumanResources.uspUpdateEmployeePersonalInfo