How to get all stored procedures in a database

Every so often you will need to get a list of objects from the database and then do something to them

In this case this script allows you to get a list of all the stored procedures in your database and grant execute to a specific user. This is handy when you want to change user and modify a lot of stored procedures

DECLARE @spName VARCHAR(255)
DECLARE @strSQL VARCHAR(5000)
DECLARE @User VARCHAR(200)
DECLARE @spPrefix VARCHAR(200)

SELECT @User = 'username'
SELECT @spPrefix ='usp_get%'

DECLARE RowCounter CURSOR FOR
SELECT [name]
FROM sys.objects
WHERE name LIKE @spPrefix AND type in (N'P', N'PC')

OPEN RowCounter
FETCH NEXT FROM RowCounter
INTO @spName

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @strSQL = 'GRANT EXECUTE ON ' + @spName +  ' TO [' +@User+ ']'

PRINT 'Granting execute on: ' + @spName

EXEC(@STRSQL)

FETCH NEXT FROM RowCounter
INTO @spName

END
CLOSE RowCounter
DEALLOCATE RowCounter

Full script here