November 9, 2008 at 5:51 pm
I'm getting a list of stored procedures in a database by:
select name from sysobjects o1
where type in ('FN', 'IF', 'TF', 'P')
and ObjectProperty(id, 'IsMSShipped') = 0
(okay, I'm getting all functions also...)
This code is returning the names of the following system stored procedures (as verified in SSMS as being in the "System Stored Procedures" folder for that database:
sp_upgraddiagrams
sp_helpdiagrams
sp_helpdiagramdefinition
sp_creatediagram
sp_renamediagram
sp_alterdiagram
sp_dropdiagram
How do I properly query the names of the non-system procs only?
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 9, 2008 at 8:04 pm
Just add:
And Not Left(name,3) = 'sp_'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 10, 2008 at 5:29 am
I assume R.Barry Young was kidding ...
select name from sysobjects o1
where type in ('FN', 'IF', 'TF', 'P')
and ObjectProperty(id, 'IsMSShipped') = 0
and name not in (select name from sys.system_objects)
Derek
November 10, 2008 at 6:47 am
Derek,
This doesn't do it... those procs must not be in sys.system_objects
Wayne
Derek Dongray (11/10/2008)
I assume R.Barry Young was kidding ...
select name from sysobjects o1
where type in ('FN', 'IF', 'TF', 'P')
and ObjectProperty(id, 'IsMSShipped') = 0
and name not in (select name from sys.system_objects)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2008 at 7:42 am
Okay, I found out how SSMS is doing it (by tracing it's actions).
It needs to be done like:
select name from sysobjects o1
where type in ('FN', 'IF', 'TF', 'P')
and ObjectProperty(id, 'IsMSShipped') = 0
and id not in (select major_id from sys.extended_properties where name = N'microsoft_database_tools_support' and minor_id = 0 and class = 1)
Seems kinda hokey to me... MS should have these procs marked as being IsMSShipped.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2008 at 8:39 am
WayneS (11/10/2008)
Seems kinda hokey to me... MS should have these procs marked as being IsMSShipped.
The flag 'isMSShipped' simply indicates that something existed in the database when it was created, i.e. it was shipped as part of the installed system.
It is possible to add procedures and get the defined as system procedures at a later date. There is an undocumented procedure, sys.sp_MS_marksystemobject, which will mark a procedure as being a system procedure ...
USE master
GO
CREATE PROC sp_generate_inserts
AS
BEGIN
--
-- code omitted for brevity
--
END
GO
--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GOAfter executing, the procedure sp_generate_inserts will be a system procedure but 'isMSShipped' will be 0. This is obviously the desired behaviour, since the procedure is not shipped by MS. 🙂
Derek
November 10, 2008 at 4:43 pm
Derek Dongray (11/10/2008)
I assume R.Barry Young was kidding ...
Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 11, 2008 at 2:02 am
rbarryyoung (11/10/2008)
Derek Dongray (11/10/2008)
I assume R.Barry Young was kidding ...Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.
Barry,
you must nbe a really lucky DBA. Personally I've seen way to much user procedures using the "sp_" prefix to even think about using your query.
[font="Verdana"]Markus Bohse[/font]
November 11, 2008 at 2:13 am
rbarryyoung (11/10/2008)
Derek Dongray (11/10/2008)
I assume R.Barry Young was kidding ...Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.
Unfortunately, in the past, I've inherited applications with a large number of user procedures with names starting 'sp_'. I've tried to educate the relevant people, but without success to date. 🙁
Of course, you are actually right (according to Microsoft's guidelines http://msdn.microsoft.com/en-us/library/ms190669.aspx) but SQL server doesn't actually stop people from doing it and doesn't even issue a warning! So some people have got into very bad habits.
Derek
November 11, 2008 at 6:23 am
Derek Dongray (11/11/2008)
rbarryyoung (11/10/2008)
Derek Dongray (11/10/2008)
I assume R.Barry Young was kidding ...Not really, "Not Left(name,3) = 'sp_'" is actually what I use. I've never had a problem with it.
Unfortunately, in the past, I've inherited applications with a large number of user procedures with names starting 'sp_'. I've tried to educate the relevant people, but without success to date. 🙁
Of course, you are actually right (according to Microsoft's guidelines http://msdn.microsoft.com/en-us/library/ms190669.aspx) but SQL server doesn't actually stop people from doing it and doesn't even issue a warning! So some people have got into very bad habits.
SQL Server may not stop it, but I do.
I do understand the inheritance problem though, but this is a site-by-site issue. Happily, most sites I have worked at it has not been an issue.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply