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

  • James_DBA

    Default port

    Points: 1422

    Comments posted to this topic are about the item Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005

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

  • René de Vries

    SSC Rookie

    Points: 29

    When I read this it made me think of a procedure we use from Codeplex: http://www.codeplex.com/ScriptDB

    We run it nightly to scripts all databases, procedures, triggers, indexes, etc.. Obviously this is very good as a backup alternative, but if you dump this regularly, you can also use this to quickly find back that previous version of a stored procedure you just (accidentally) overwrote 🙂

    great for inclusion in SVN.

    René

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice one ...:)

  • ChiragNS

    One Orange Chip

    Points: 26137

    nice article

    "Keep Trying"

  • Knut Boehnert

    SSCrazy

    Points: 2947

    Sorry, but this part...

    One last note, if you want to create an SP and have it distributed (automatically included) with all databases you create then you will want to create the SP for the 'master' database. Also, this only gets used with newly created databases from the point after you created the SP...So, if you already have existing databases you will still have to create the SP for each one of them.

    ...doesn't make sense.

    It would make sense if instead of master you would write model.

    Nice article and I am currently testing things...:w00t:

  • ralf.lulay

    SSC Rookie

    Points: 27

    sometimes when i'm searching for a particular procedure....i use the following:

    use [your DB here]

    GO

    SELECT Distinct SO.Name

    FROM sysobjects SO (NOLOCK)

    INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

    AND SO.Type = 'P'

    AND SC.Text LIKE '%YOUR SEARCH TEXT HERE e.g. TABLENAME%'

    ORDER BY SO.Name

  • sri-281581

    Newbie

    Points: 7

    I believe using information_schema.routines is still a better idea, code using this will work across SQL server versions..

  • blandry

    SSCarpal Tunnel

    Points: 4821

    Thanks very much for this informative and helpful article. Just last week we were discussing cleaning up our test/development database and this information is timely and very handy. Great work!

    There's no such thing as dumb questions, only poorly thought-out answers...
  • JuncTheMayl

    Hall of Fame

    Points: 3882

    "SQL Server 2005 only stores the identifying information of a SP."

    actually, SQL05 DOES store the code of the object itself as well. it can be found in sys.sql_modules, and a join can be used from sys.all_objects to sys.sql_modules on object_id. there are some catches though, so you may not get to see the code for ALL objects (especially system objects).

  • deanroush

    SSC-Addicted

    Points: 474

    Although I agree that using 'sys.' views is a better way to get information, I have used the text column of the syscomments table directly in the past to get the DDL (data description language) for the script-level definitions of stored procedures, views, default values, etc. I do not use this table (directly) for production level code.

  • sbateman-803743

    SSC Veteran

    Points: 222

    Look at: GeckoWare SQL Scribe for Microsoft SQL Server.

    www.ag-software.com

    Main non-standard use is Comparing DB Versions [Tables/SP/Function/Views...]

    Upgrade are supplied by our ERP programmers - I can see what they have changed in new release and which parts of database are 'our bespoke'

  • James_DBA

    Default port

    Points: 1422

    Thank you to everyone for the great comments on additional methods to obtain the same data; and especially to those that have methods that could improve on the speed of obtaining this data!

    Lenny Garza, you are correct that the definitions are stored within SQL Server 2005 sys.sql_modules view; I had intended for that line to imply that the 'how' to use these SPs still may require BOL to understand their usages. I'm actually already working on an article that touches upon this subject. Thank you for pointing out the confusion of that sentence.

    Thank you,

    James Rea

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

  • fuller.artful

    Right there with Babe

    Points: 795

    I was just about to hit the same button, when I spotted your reply. If you want a proc to appear in every database you create (subsequent to adding it), place it in model not master. I have experimented with this extensively, to create versions of model that are greared toward specific problems such as order-entry systems or accounting systems or HR systems. I back up model, add the stuff I'm sure I'm going to need, save it and then rename it to something like model_OrderEntry. Then when I have to build one of these, I just rename model to model_virgin and model_OrderEntry to model, then create the database. Not much overhead

    Arthur

    Arthur Fuller
    cell: 647-710-1314

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

  • wchaster

    Old Hand

    Points: 392

    The way I find out what stored procedures are in a database is in Management Studio, to drill into the Programmability | Stored Procedures | System Stored Procedure. Here you can find the sys or user created listing. I like this as it easily allows me to what they are made of, the thing I need to learn is how to use them 😀

    What ever happened to programmers documenting there code? 😉

    😀
    ACN is the world's largest direct seller of telecommunications and essential services.
    http://helpu.acndirect.com/
    ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
    destinations around the world, including India, Mexico and the UK!
    :hehe:

  • vinsat

    SSC Rookie

    Points: 31

    Information provided helps us. Have a question here:When we create a database, it inherit the properties from MODEL database. In this case if we create a stored procedure in master database how it will be distributed in all the other user databases.

Viewing 15 posts - 1 through 15 (of 35 total)

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