InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 19:37:21 GMT20http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxGreat, Thanks! Will try it out..:DFri, 07 Nov 2008 10:04:26 GMTSlick84http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxIn 2000 all objects are in sysobjects and the definitions are in syscomments in the text columnFri, 07 Nov 2008 09:59:24 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxCool.. so then.. how can I pull of this same trick while having SQL Server 2000 as the backend DB ? I know sys.objects does not exist...but can't think of a work-around..hmm..Fri, 07 Nov 2008 09:48:17 GMTSlick84http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxYes it is.Management Studio is just a client tool. Your queries are going towards the actual server which in your case is of version 2000Fri, 07 Nov 2008 09:40:45 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxI'm running SSMS , SQL Server 2005. Was running this query on a DB which has a backend on SQL Server 2000. Could that be it?Fri, 07 Nov 2008 09:35:59 GMTSlick84http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThe reason could be that you are running SQL Server 2000 that did not have this table.Please, confirmFri, 07 Nov 2008 09:29:11 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxWhen I run the query I get the following error:[code]Msg 208, Level 16, State 1, Line 1Invalid object name 'sys.all_objects'.[/code]What could be the issue?Thanks,OFri, 07 Nov 2008 09:14:12 GMTSlick84http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThanks James, it is really helpful to me.Fri, 08 Aug 2008 03:06:55 GMTarup chakrabortyhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxCheers 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 :-)Wed, 30 Jul 2008 01:58:01 GMTtwillcomphttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxKids,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_objectsWHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')ORDER BY [name];GOTue, 29 Jul 2008 12:41:42 GMTYelena Varshalhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxLimeyTone,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 ReaTue, 29 Jul 2008 10:31:11 GMTJames_DBAhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxI 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_alterdiagramsp_creatediagramsp_dropdiagramsp_helpdiagramdefinitionsp_helpdiagramssp_renamediagramsp_upgraddiagramsThere may be more.cheersTonyTue, 29 Jul 2008 09:59:49 GMTtwillcomphttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxarup_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 ReaTue, 29 Jul 2008 09:39:02 GMTJames_DBAhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxAbhijit,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 ReaTue, 29 Jul 2008 09:16:55 GMTJames_DBAhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspx"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?Tue, 29 Jul 2008 03:33:02 GMTarup chakrabortyhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxin sql 2005 we have so many ways u can list out the requird objects,u can get the list of procedures using..sys.proceduresTue, 29 Jul 2008 01:22:33 GMTabmorehttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxActually, 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 ReaTue, 29 Jul 2008 00:32:26 GMTJames_DBAhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspx[quote]Legal Disclaimer/WarningPlease remember that when using an SP you are not sure the outcome or effect it has should be done on [b]test systems[/b]; never use anything that has not been thoroughly tested in a [b] production environment.[/b] [/quote]Has anyone read the Legal Disclaimer. I think [b]test sytem [/b]and [b]production environment[/b] are mistakenly shuffled or it is me who is having a bad frozen sight today. :PTue, 29 Jul 2008 00:01:48 GMTAnam Vermahttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThat 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!ArthurMon, 28 Jul 2008 15:47:36 GMTfuller.artfulhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThank 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 ReaMon, 28 Jul 2008 15:23:13 GMTJames_DBAhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxInformation 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.Mon, 28 Jul 2008 14:41:15 GMTvinsathttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThe 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 :DWhat ever happened to programmers documenting there code? ;)Mon, 28 Jul 2008 12:50:11 GMTwchasterhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxI 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 [i]model[/i] not [i]master[/i]. 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 overheadArthurMon, 28 Jul 2008 12:05:52 GMTfuller.artfulhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThank 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 ReaMon, 28 Jul 2008 09:53:12 GMTJames_DBAhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxLook at: GeckoWare SQL Scribe for Microsoft SQL Server. [u]www.ag-software.com[/u]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'Mon, 28 Jul 2008 09:03:41 GMTsbateman-803743http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxAlthough 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.Mon, 28 Jul 2008 08:55:36 GMTdeanroushhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspx"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).Mon, 28 Jul 2008 07:12:50 GMTLenny-319914http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxThanks 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!Mon, 28 Jul 2008 06:33:49 GMTblandryhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxI believe using information_schema.routines is still a better idea, code using this will work across SQL server versions..Mon, 28 Jul 2008 03:45:11 GMTsri-281581http://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxsometimes when i'm searching for a particular procedure....i use the following:use [your DB here]GOSELECT 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.NameMon, 28 Jul 2008 02:43:09 GMTralf.lulayhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxSorry, 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 [i]master [/i]you would write [i]model[/i].Nice article and I am currently testing things...:w00t:Mon, 28 Jul 2008 02:32:22 GMTKnut Boehnerthttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxnice articleMon, 28 Jul 2008 02:21:07 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxNice one ...:)Mon, 28 Jul 2008 02:20:01 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxWhen I read this it made me think of a procedure we use from Codeplex: [url=http://www.codeplex.com/ScriptDB]http://www.codeplex.com/ScriptDB[/url] 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éMon, 28 Jul 2008 01:06:51 GMTRené de Vrieshttp://www.sqlservercentral.com/Forums/Topic541425-1157-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/63471/">Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005</A>[/B]Sat, 26 Jul 2008 11:30:26 GMTJames_DBA