Counting Stored Procedures

I ran out of fingers...
How can I count the number of stored procedures for which the name starts
with "XYZ_"
Thanks!SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_name LIKE 'XYZ[_]%'
AND routine_type = 'PROCEDURE'
David Portas
SQL Server MVP
--|||select count(*) from sysobjects where name like 'xyz\_%' escape '' and
objectproperty(id, 'IsProcedure') = 1
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
> I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
> with "XYZ_"
> Thanks!
>|||Use <dbname>
Go
Select Count(*)
From dbo.sysobjects as S
Where ObjectProperty(S.id, 'IsProcedure') = 1
And ObjectProperty(S.id, 'IsMSShipped') = 0
And Name Like 'XYZ%'
Granted, there aren't any Microsoft shipped stored procs that begin with 'XY
Z',
so you could skip that part of the criteria.
Thomas
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
>I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
with
> "XYZ_"
> Thanks!
>|||Can my response. David's response is more portable, using the
INFORMATION_SCHEMA views. I need to remember I'm supposed to use those...
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O8kkPZBTFHA.544@.TK2MSFTNGP15.phx.gbl...
> select count(*) from sysobjects where name like 'xyz\_%' escape '' and
> objectproperty(id, 'IsProcedure') = 1
> Mike
> "Guadala Harry" <GMan@.NoSpam.net> wrote in message
> news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
starts
>|||Select * From sysObjects
Where type = 'P'
And name Line 'XYZ_%'
"Guadala Harry" wrote:

> I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
> with "XYZ_"
> Thanks!
>
>|||ditto Mike's comment
"CBretana" wrote:
> Select * From sysObjects
> Where type = 'P'
> And name Line 'XYZ_%'
>
> "Guadala Harry" wrote:
>|||It's because Microsoft doesn't support queries on system tables. The table
structure may be changed in future releases.
For a one time deal though, no problem, as long as you don't include it in
production code.
Ditto CBretana
Just kidding. I know I don't have to remind you. Let's just chalk it up to a
long, hard day... or w. :-)
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ODF4HcBTFHA.548@.tk2msftngp13.phx.gbl...
> Can my response. David's response is more portable, using the
> INFORMATION_SCHEMA views. I need to remember I'm supposed to use those...
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:O8kkPZBTFHA.544@.TK2MSFTNGP15.phx.gbl...
> starts
>