Pages

Monday, January 3, 2011

List of procedures as comma seprated string

Following is the code that can be used to get  list of all procedures having name started with string 'Proc' and this returns a single string containing all procedures as comma separated names.

i.e. if there are procedures proc_1 ,proc_2,proc_3 and proc_4 exists in sql server then the returned result will look like

proc_1 ,proc_2,proc_3 , proc_4

Also  @listStr variable contains the result that can be used to process this list further according to the requirements.

DECLARE @listStr VARCHAR(MAX)
select @listStr = COALESCE(@listStr+',' , '') + [name]
FROM sysobjects WHERE type ='P' AND name LIKE 'Proc%' AND [name]  IS NOT NULL
SELECT @listStr

No comments: