How do I find a text in SQL Object ?
03-Dec-0909 Leave a comment
Started with:
Adapted to:
ALTER
PROCEDURE [Testing].[uspFindText]@DatabaseName
nvarchar(50) = ‘EFI’, @TextToFind nvarchar(100)
–, @Type int = 0 –0 = ALL, 1 = PROCEDURE
AS
DECLARE
@SQL nvarchar(500)SELECT @SQL = ‘SELECT ROUTINE_TYPE, SPECIFIC_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
FROM [‘ + @DatabaseName + ‘].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ”%’ + @TextToFind + ‘%”’
–SELECT @SQL = @SQL + ‘ AND ROUTINE_TYPE=”PROCEDURE”’
EXECUTE(@SQL)