How to list all Procedures and Functions with sp

1 Sep 2006 11:09 AM

Andreas Klemt

Hello,

how can I easily list all Procedures or Functions with a sp_xxx?
Is there also a fast way with sp_xxx to find Procedures?

Thanks for any help in advance!

Regards
Andreas Klemt

1 Sep 2006 11:29 AM

Erland Sommarskog

Andreas Klemt (aklem***@hotmail.com) writes:

> how can I easily list all Procedures or Functions with a sp_xxx?
> Is there also a fast way with sp_xxx to find Procedures?

On SQL 2000, the easiest is to say

   SELECT name, xtype
   FROM   sysobjects
   WHERE xtype IN ('P', 'FN', 'TF', 'IF')

On SQL 2005, the best is to say:

   SELECT name, type
   FROM   sys.objects
   WHERE type IN ('P', 'PC', 'AF', 'FN', 'TF', 'IF', 'FT', 'FS')

The list of types is longer, thanks the possibility to write things in
the CLR.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

1 Sep 2006 11:38 AM

Andreas Klemt

Hello Erland,

thanks a lot for your fast reply!
That helped me.

Kind Regards
Andreas Klemt

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> schrieb im Newsbeitrag
news:Xns9831893761C1BYazorman@127.0.0.1...
> Andreas Klemt (aklem***@hotmail.com) writes:
>> how can I easily list all Procedures or Functions with a sp_xxx?
>> Is there also a fast way with sp_xxx to find Procedures?
>
> On SQL 2000, the easiest is to say
>
>   SELECT name, xtype
>   FROM   sysobjects
>   WHERE xtype IN ('P', 'FN', 'TF', 'IF')
>
> On SQL 2005, the best is to say:
>
>   SELECT name, type
>   FROM   sys.objects
>   WHERE type IN ('P', 'PC', 'AF', 'FN', 'TF', 'IF', 'FT', 'FS')
>
> The list of types is longer, thanks the possibility to write things in
> the CLR.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

1 Sep 2006 11:11 PM

Hugo Kornelis

On Fri, 1 Sep 2006 13:09:25 +0200, Andreas Klemt wrote:

>Hello,
>
>how can I easily list all Procedures or Functions with a sp_xxx?
>Is there also a fast way with sp_xxx to find Procedures?
>
>Thanks for any help in advance!

Hi Andreas,

Smalll addition to Erland's suggestion:

   SELECT name, xtype
   FROM   sysobjects
   WHERE xtype IN ('P', 'FN', 'TF', 'IF')
--   WHERE type IN ('P', 'PC', 'AF', 'FN', 'TF', 'IF', 'FT', 'FS')
   AND    name LIKE 'sp[_]%';

--
Hugo Kornelis, SQL Server MVP

How to list all Procedures and Functions with sp_xxxx?