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

  • 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/

  • 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

  • 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. 😛

  • 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/

  • 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

  • "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?

  • 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/

  • 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/

  • 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

  • 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/

  • 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

  • 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 🙂

  • Thanks James, it is really helpful to me.

  • 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:

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

    Please, confirm

    Regards,Yelena Varsha

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

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