ユーザーが定義したストアドプロシージャ一覧の取得

質問

SQL Server 2005で、
db内のストアドプロシージャ一覧を取得したいと思っています。

select o.*
from syscomments c , sysobjects o
where c.id = o.id and o.type='P'
order by o.name

といったように記述すれば一覧は取得できるようですが、
これだとシステムストアドプロシージャも表示されてしまいます。
ユーザーで定義したストアドプロシージャのみ取得したいのですが、可能でしょうか？

何か良い手段をご存じの方がいらっしゃいましたらアドバイス頂けますと嬉しいです。
どうぞよろしくお願いいたします。

回答No.2

Siegrune · ベストアンサー

ANo1です。
なんか、sysobjects と procedures とをごっちゃにしてました。

SELECT *
FROM sys.procedures
WHERE
schema_id NOT IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA'))
ORDER BY name
の結果なんかはどうでしょう？

sp_creatediagram、sp_alterdiagram、sp_dropdiagram
そのたデータベースダイアグラム関連のストアドプロシジャが
まざるようですが。
where句に
AND (name NOT LIKE 'sp_%diagram%')
とつけたら使えそう（但し、ユーザプロシジャが同じ系統の名前になっているとダメですが。）

お礼

回答ありがとうございます。
期待通りの結果が得られました。
確かにデータベースダイアグラム関連のストアドプロシジャは混ざってしまうようですが（これはしょうがないようですかね）、
AND (name NOT LIKE 'sp_%diagram%')
を加えることで除けました。

助かりました。ありがとうございますm(_ _)m

回答No.1

Siegrune

マニュアル見ていると、
select o.*
from syscomments c , sysobjects o
where c.id = o.id and o.type='P'
and (o.schema_id NOT IN (schema_id('sys'), schema_id('INFORMATION_SCHEMA')))
order by o.name
でうまくいきそうに見えますが、
・・・データベースダイアグラム関連のオブジェクトは、作ったユーザのスキーマに所属するみたいで
だめかもしれません。
（私が試しているのがデータベースの所有者が作ったオブジェクトですので
　そちらではうまくいくかも。）

あるいは、

SELECT uid,name from sysusers
をみて、例えば、結果が、
dboというユーザがuid=1
sysというユーザがuid=4
とすると、

select o.*
from syscomments c , sysobjects o
where c.id = o.id and o.type='P'
and o.uid <> 1 and o.uid <> 4
order by o.name
で、ユーザーが作ったプロシジャだけになるのですが、
問題は、データベースの所有者であるユーザが作ったプロシジャは、
dboと同じ扱いになったはずなので上のパターンと同じ結果になるかも。

vb2005とかでデータベース接続したときのストアドプロシジャ一覧にはデータベースダイアグラム関連
のストアドプロシジャも出てこないので何か識別はできるはず、なんですが、良く分からないです。
・・・数が少ないので、固定のnameを除外しているのかな？

といったところです。

お礼

回答ありがとうございました！
上記クエリ実行したところ、

列名 'schema_id' が無効です。

と言われてしまいました(*_*
sysobjectsで抽出した結果を見ると、システムストアドとユーザーストアドの違いが見つからず、sysobjectsで判断するのは難しいのかな…
nameがsp_で始まるものを除外、といったところなのでしょうか…。

もう少し悩んでみます。