List all Stored Procedures and Views

Question

Post reply

List all Stored Procedures and Views

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
April 9, 2008 at 10:28 am

#186138

Hello Everyone
I hope that your day is going well.
I would like to query out of each database, all the stored procedures and views. I cannot remember what table these are stored in. Can someone help to jar my memory.
I think that I need to start taking some ginko biloba. 😀
Thanks
Andrew SQLDBA

Viewing 6 posts - 1 through 5 (of 5 total)

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

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

These two catalog views should show that.

SELECT * FROM sys.views

SELECT * FROM sys.procedures

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thanks

I was thinking about the sysobjects table.

SQL 2005 has the items named appropriately, where SQL 2000 was not as much

Thanks

Andrew SQLDBA

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Todd Engen (4/9/2008)
These two catalog views should show that.
SELECT * FROM sys.views
SELECT * FROM sys.procedures

By the way - sys.views returns all USER views, which sounds like what you want....

There's also sys.system_views and sys.all_views if either one fits your needs better.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

if you care about ANSI compliance, better to use INFORMATION_SCHEMA views. but who cares about that... 🙂

---------------------------------------
elsasoft.org

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

jezemine (4/9/2008)
if you care about ANSI compliance, better to use INFORMATION_SCHEMA views. but who cares about that... 🙂

absolutely true - if you want just the public views and you're not looking for all of the stuff you can get from sys.views...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?