select definition from sys.sql_modules where definition like '%' + @searchstring + '%'

/*** Extract SQL text for all user functions and procs ************************
**
** Charlie (2010-Feb-18)
**
******************************************************************************/

SET NOCOUNT ON

DECLARE @object VARCHAR(MAX)
DECLARE @type VARCHAR(2)
DECLARE @text VARCHAR(MAX)
DECLARE @error INT

IF OBJECT_ID('tempdb..#sqlText') IS NOT NULL DROP TABLE #sqlText
IF OBJECT_ID('tempdb..#objects') IS NOT NULL DROP TABLE #objects

CREATE TABLE #sqlText (
	[Id] INT IDENTITY(1,1)
	, [line] NVARCHAR(255)
	)

CREATE TABLE #objects (
	[name] VARCHAR(255)
	, [type] VARCHAR(255)
	, [text] VARCHAR(MAX)
	)

DECLARE recCursor CURSOR LOCAL READ_ONLY FOR
SELECT
	[name]
	, [type]
FROM
	sys.Objects
WHERE
	[Type] IN ('P', 'FN', 'V')
	AND [is_MS_shipped] = 0

OPEN recCursor

	FETCH NEXT FROM recCursor INTO @object, @type

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		-- display the object
		RAISERROR(@object, 0 ,1) WITH NOWAIT
	
		SET @text = ''
		TRUNCATE TABLE #sqlText

		-- Get the TSQL for the current object
		INSERT #sqlText EXEC sp_helpText @object

		-- Concatenate all lines together into a dynamic SQL string
		SELECT @text = @text + [Line] FROM #sqlText ORDER BY [Id] ASC
		
		-- Tidy up multiple spaces
		WHILE ( CHARINDEX('  ', @text) > 0 ) SET @text = REPLACE(@text, '  ', ' ')

		-- Change the CREATE x TO ALTER x
		SELECT @text = REPLACE(@text, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
		SELECT @text = REPLACE(@text, 'CREATE FUNCTION', 'ALTER FUNCTION')


		-- Put the object into our list
		INSERT #objects ([name], [type], [text])
		SELECT @object, @type, @text

		FETCH NEXT FROM recCursor INTO @object, @type

	END

CLOSE recCursor
DEALLOCATE recCursor

-- do your seach here

SELECT * FROM #objects

quote:

---

Originally posted by qman

Once again Visakh16, thanks! You guys rock!

On a side note, can this logic be applied to search throught all Views?

---

SELECT sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o 
ON sm.object_id = o.object_id
where sm.definition like '%' + @searchstring + '%' 
and o.type='v'