Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Creating Functions for Stored Procedures Expand / Collapse
Author
Message
Posted Friday, October 10, 2008 6:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 35,584, Visits: 32,174
jcrawf02 (10/10/2008)
Just curious, if you are working in an environment where DBA's DON'T know not to use sp_, is there another way to filter out the system procs and keep the rest?


Yes...

If you use Information_Schema.Routines as the source of information, only user objects are listed. You can also brute force things using one of the following...

WHERE OBJECTPROPERTY(id , 'IsMsShipped') = 0

WHERE OBJECTPROPERTY(OBJECT_ID(tablenamecolumn) , 'IsMsShipped') = 0

WHERE OBJECTPROPERTY(OBJECT_ID(schemanamecolumn+'.'+tablenamecolumn) , 'IsMsShipped') = 0




--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #583899
Posted Friday, October 10, 2008 6:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:19 PM
Points: 27, Visits: 311
Thanks to everyone for good response (and bad as well). Sorry for broken C# link, but it’s nothing to do with the article.
Post #583900
Posted Friday, October 10, 2008 6:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 16, 2014 6:19 PM
Points: 27, Visits: 311
You could use select * from sys.objects where is_ms_shipped = 0 as well. I am not INFORMATION_SCHEMA fan. This is personal preference.:D
Post #583901
Posted Friday, October 10, 2008 8:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
The link has been corrected in the article.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #583994
Posted Friday, October 10, 2008 7:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:32 PM
Points: 35,584, Visits: 32,174
Alex Grinberg (10/10/2008)
You could use select * from sys.objects where is_ms_shipped = 0 as well. I am not INFORMATION_SCHEMA fan. This is personal preference.:D


Heh... I forgot about that. I'm still a bit stuck in the world of 2k. Thanks for the reminder. :)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #584321
Posted Tuesday, October 14, 2008 11:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:47 PM
Points: 153, Visits: 168
Alex:
Technical folks are busy people. Like me, they probably scan through a lot of articles very quickly, the vast majority of which are either (a) not relevant to them or (b) poor code or poor writing. Because I find your writing reasonable and your topics interesting, I thought it would be worthwhile to offer one tip, as a fellow author (http://cleancode.sourceforge.net/wwwdoc/articles.html). The general principal that applies to many things is this:

Make it easy for people to do business with you.

Applying this to the context of writing this becomes:

Make it easy for people to see why your article is useful to them.

I almost skipped your article because I had to scan and rescan and rescan again until I could say "Oh, yes. That is useful to know." What I would have found most useful--and no doubt others would as well--is to FIRST provide a concrete example, as in: "Say in your DB you have a GetMapDetails stored procedure which takes parameters x and y, and a InsertNewCustDetails stored procedure which takes parameters a, b, and c. If you run my SQL-to-.NET function creator, this will provide a .cs or .vb file containing this code: ."
Post #585696
Posted Tuesday, October 28, 2008 12:15 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Saturday, September 27, 2014 12:26 PM
Points: 676, Visits: 433
A couple of options depending on your version of SQL to exclude microsoft objects.

In SQL 2005, there is a field called is_ms_shipped. It's a bit value. 1 means it's a microsoft object, 0 if it is not.
SELECT name 
FROM sys.objects
WHERE type = 'p' and name not like 'sp[_]%' AND is_ms_shipped=0
ORDER BY name

To use this in SQL 2000 you would modify the SQL code to this:
SELECT name 
FROM sysobjects
WHERE type = 'p' and name not like 'sp[_]%' AND OBJECTPROPERTY(object_id,'isMSShipped')=0
ORDER BY name


Thanks for sharing this code.



Post #593155
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse