Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005

Question

Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005

Viewing 15 posts - 16 through 30 (of 34 total)

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

James_DBA Default port Points: 1422 More actions · Answer 1

Thank you for the nice remarks.

As pointed out in previous posts; the correct location to create stored procedures that are distributed to new databases is to use the 'model' database. This was an error in the article. I apologize for any inconvenience it may have caused.

Thank you,

James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

fuller.artful Right there with Babe Points: 795 More actions · Answer 2

That small error aside, you have led the way. I rewrote your stuff into a table-udf which accepts the object type as a parameter and it works beautifully. I can extract lists of procs, scalar udfs, table udfs, views and so on. Thanks to your initial code, I've got some magic now. I rewrote your code to be a table udf so I can pass parms to it and obtain all or nothing, depending on my interest at the moment.

Thanks for the pointers!

Arthur

Arthur Fuller
cell: 647-710-1314

Only two businesses refer to their clients as users: drug-dealing and software development.
-- Arthur Fuller

Anam Verma SSCertifiable Points: 5365 More actions · Answer 3

Legal Disclaimer/Warning
Please remember that when using an SP you are not sure the outcome or effect it has should be done on test systems; never use anything that has not been thoroughly tested in a production environment.

Has anyone read the Legal Disclaimer. I think test sytem and production environment are mistakenly shuffled or it is me who is having a bad frozen sight today. 😛

James_DBA Default port Points: 1422 More actions · Answer 4

Actually, the sentence is correct but can easily be mis-read.

The second part of the sentence is saying that you should not use anything you have not thoroughly tested in a production environment. It probably would've done better being placed into two complete sentences.

Thank you,

James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

abmore Hall of Fame Points: 3628 More actions · Answer 5

in sql 2005 we have so many ways u can list out the requird objects,

u can get the list of procedures using..sys.procedures

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 6

"Which, of course, the 'master' database is the "template" database used whenever you create a new database (thus, the SPs within the 'master' database are automatically created for the databases you create)"

I think this will be model instead of master. Bkz model database works as a template database for all other.

Also, the sysobject view gives us the same results, any difference between sysobjects and all_objects?

James_DBA Default port Points: 1422 More actions · Answer 7

Abhijit,

The problem with using sys.procedures is it ONLY lists out the user created Stored Procedures; this article was in regards to finding ALL procedures (including the MS provided SPs and other programmable objects).

Thank you,

James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

James_DBA Default port Points: 1422 More actions · Answer 8

arup_kc,

Essentially there is no difference between the two views except for when they were created; however, sysobjects is a SQL 2000 view and was left in SQL 2005 for backward compatability reasons which would make it subject to deprecation before sys.all_objects view.

The proper (or recommended) view in SQL 2005 that replaced sysobjects is sys.objects. Which if you use the sys.objects view you will only see user-defined and schema scoped objects. You'd then have to query the sys.system_objects (then use the two tables together) to obtain a complete listing of the system objects and the user-defined objects. Obviously, using a single table/view would be preferred in most cases.

I chose to use sys.all_objects because it already lists both system and user-defined objects and is a SQL 2005 view.

Thank you,

James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

twillcomp SSC Eights! Points: 989 More actions · Answer 9

I believe some care is needed if you are relying on the is_ms_shipped flag being set to 1 for all MS shipped procedures. I get a list of stored procedures which I think are diagramming procedures that are not user defined procedures.

For example:

sp_alterdiagram

sp_creatediagram

sp_dropdiagram

sp_helpdiagramdefinition

sp_helpdiagrams

sp_renamediagram

sp_upgraddiagrams

There may be more.

cheers

Tony

James_DBA Default port Points: 1422 More actions · Answer 10

LimeyTone,

Those SPs you listed in regards to not having the "Is_MS_Shipped" flag should, in fact, NOT have the flag. They should not be marked as being shipped from Microsoft because they were not a part of the SQL 2005 distribution stored procedures.

Those SPs are actually created from Visual Studio; typically you will see them in SQL instances where VS was used to create a diagram. I'm sure there are other ways these procedures could become installed on a SQL instance.

Here is a link that specifically states that VS will create these Stored Procedures: http://msdn.microsoft.com/en-us/library/ms171974.aspx. You'll notice this list exactly matches your list.

I think in this instance we can see the value in being able to view a listing of stored procedures; in this case you were unaware that VS created these. Maybe there are more stored procedures you are not aware of that have been created?

Thanks,

James Rea

~ Without obstacles, you cannot progress ~
http://sqln.blogspot.com/

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 11

Kids,

Try this: it will print all your SPs text. I just modified the original query from the article to include OBJECT_DEFINITION function. You have to set Query Results In Text to use it.

SELECT '--*****************************************'

+Char(13)+Char(10)+ '--'+Char(9) +Name

+Char(13)+Char(10)+ '--'

+Char(13)+Char(10)

+'--*****************************************'

+Char(13)+Char(10)+ '--'

,OBJECT_DEFINITION ( object_id (name))

FROM sys.all_objects

WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')

ORDER BY [name];

GO

Regards,Yelena Varsha

twillcomp SSC Eights! Points: 989 More actions · Answer 12

Cheers for this James I didn't know this.

The point I made about being careful if you rely on the flag to identify user defined procs still holds. I'm sure there might be someone out there who tries to list all user procs for some dodgy maintenance process who will end up messing with procs they didn't intend to.

Your warning about knowing what you are running before you run it holds true here!

PS. If I was being picky, I'd ask who ships VS 🙂

arup chakraborty Hall of Fame Points: 3640 More actions · Answer 13

arup chakraborty

Hall of Fame

Points: 3640

August 8, 2008 at 3:06 am

#854986

Thanks James, it is really helpful to me.

Slick84 SSCertifiable Points: 5602 More actions · Answer 14

When I run the query I get the following error:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.all_objects'.

What could be the issue?

Thanks,

O

--
:hehe:

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 15

The reason could be that you are running SQL Server 2000 that did not have this table.

Please, confirm

Regards,Yelena Varsha