Identifying the number of Sp''s without I/p parameters

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES t1

WHERE NOT EXISTS

(SELECT * FROM INFORMATION_SCHEMA.PARAMETERS t2

WHERE t1.SPECIFIC_NAME = t2.SPECIFIC_NAME

AND PARAMETER_MODE NOT LIKE 'IN')

AND OBJECTPROPERTY(OBJECT_ID(t1.SPECIFIC_NAME),'IsMSShipped')=0

You might need to tweak this a little bit, since I use the .PARAMETER views not quite often.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rafeeque M.K. Ten Centuries Points: 1205 More actions · Answer 2

I think this may also work

SELECT COUNT (*) FROM SYSOBJECTS WHERE type = 'P'

Rafi-

M.C Praveen Chandra Newbie Points: 2 More actions · Answer 3

Hello Frank Kalis,

Thanks you for the solution, i have tweaked the query like this

Select NAME from sysobjects where type='P'

AND

NAME NOT IN (

select DISTINCT SPECIFIC_NAME from INFORMATION_SCHEMA.Parameters) to select the sp's which do not have both Input and Output parameters.

Thanks for the solution,

Regards

Praveen