query to seaarch out the table and sps and table name in stored procedure

SELECT * FROM sysobjects o WHERE (o.xtype = 'P') ORDER BY o.name

--Select * from sysobjects where type = 'U' and category = 0

select

so
.name,

sc
.text

from

sysobjects so
inner join syscomments sc on so.id = sc.id

where

sc
.text like '%ROLES%'

and sc.text like '%select%'

-- or sc.text like '%WorkGroup%'

LastIndexOf function in sql server

CREATE
FUNCTION LastIndexOf

(@strValue VARCHAR(4000),

@strChar
VARCHAR(50))

RETURNS
INT

AS

BEGIN

DECLARE
@index INT

SET
@index = 0

WHILE
CHARINDEX(@strChar, @strValue) > 0

BEGIN

SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1

THEN

(LEN(@strValue) - LEN(SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + LEN(@strChar),LEN(@strValue))))

ELSE

1

END

SET @strValue = SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + len(@strChar),LEN(@strValue))

END

RETURN @index

END

Recursive function in sql server

alter
FUNCTION dbo.GetParents

(
@id int,@parents varchar(max))

RETURNS
varchar(max)

AS

BEGIN

declare
@itemPkey int

IF EXISTS (SELECT LKP_LKP_PKEY_Level_0 FROM dbo.APP_LOOKUP_TABLE WHERE

LKP_TYP_PKEY
= @id and LKP_LKP_PKEY_Level_0 is not null)

BEGIN

SELECT top 1 @itemPkey=LKP_LKP_PKEY_Level_0 FROM dbo.APP_LOOKUP_TABLE WHERE

LKP_TYP_PKEY
= @id and LKP_LKP_PKEY_Level_0 is not null

set @parents = @parents +',' + (SELECT cast(LKP_TYP_PKEY as varchar(10)) FROM dbo.APP_LOOKUP_TABLE where LKP_PKEY=@itemPkey)

SELECT @parents = dbo.GetParents(LKP_TYP_PKEY, @parents) FROM dbo.APP_LOOKUP_TABLE where LKP_PKEY=@itemPkey

END

RETURN @parents

END