Getting Sprocs that are not MS Shipped

Awhile back somebody showed me a neat little trick to filter out the stupid
dt tables and etc. I am wondering if there is a similar but different trick
for procs because I tried running this same query but changed U to P and it
did not filter the dt. and other useless sprocs.
@."SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') = 0)
ORDER BY category DESC, name";
dt_ procs are created when you open the diagrams pane in Enterprise Manager.
For some reason they are not marked as IsMsShipped = 1, so the only other
way to filter them out (other than deleting them, and resisting the urge to
open the Diagrams folder again) is to add a filter on the procedure name.
Also, you should use INFORMATION_SCHEMA views, not sysobjects.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
Of course this assumes you haven't created your own procs named dt_whatever.
(In fact, when I use INFORMATION_SCHEMA.ROUTINES instead of sysobjects, the
dt_ procs are not included, even without the additional WHERE clause. Maybe
this change alone will resolve the situation for you.)
http://www.aspfaq.com/
(Reverse address to reply.)
<recoil@.community.nospam> wrote in message
news:ulSRIWbHFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Awhile back somebody showed me a neat little trick to filter out the
stupid
> dt tables and etc. I am wondering if there is a similar but different
trick
> for procs because I tried running this same query but changed U to P and
it
> did not filter the dt. and other useless sprocs.
>
> @."SELECT name
> FROM sysobjects
> WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') =
0)
> ORDER BY category DESC, name";
>
>
|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.
|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.
sql

Getting Sprocs that are not MS Shipped

Awhile back somebody showed me a neat little trick to filter out the stupid
dt tables and etc. I am wondering if there is a similar but different trick
for procs because I tried running this same query but changed U to P and it
did not filter the dt. and other useless sprocs.
@."SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') = 0)
ORDER BY category DESC, name";dt_ procs are created when you open the diagrams pane in Enterprise Manager.
For some reason they are not marked as IsMsShipped = 1, so the only other
way to filter them out (other than deleting them, and resisting the urge to
open the Diagrams folder again) is to add a filter on the procedure name.
Also, you should use INFORMATION_SCHEMA views, not sysobjects.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
Of course this assumes you haven't created your own procs named dt_whatever.
(In fact, when I use INFORMATION_SCHEMA.ROUTINES instead of sysobjects, the
dt_ procs are not included, even without the additional WHERE clause. Maybe
this change alone will resolve the situation for you.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
<recoil@.community.nospam> wrote in message
news:ulSRIWbHFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Awhile back somebody showed me a neat little trick to filter out the
stupid
> dt tables and etc. I am wondering if there is a similar but different
trick
> for procs because I tried running this same query but changed U to P and
it
> did not filter the dt. and other useless sprocs.
>
> @."SELECT name
> FROM sysobjects
> WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') =0)
> ORDER BY category DESC, name";
>
>|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.

Getting Sprocs that are not MS Shipped

Awhile back somebody showed me a neat little trick to filter out the stupid
dt tables and etc. I am wondering if there is a similar but different trick
for procs because I tried running this same query but changed U to P and it
did not filter the dt. and other useless sprocs.
@."SELECT name
FROM sysobjects
WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') = 0)
ORDER BY category DESC, name";dt_ procs are created when you open the diagrams pane in Enterprise Manager.
For some reason they are not marked as IsMsShipped = 1, so the only other
way to filter them out (other than deleting them, and resisting the urge to
open the Diagrams folder again) is to add a filter on the procedure name.
Also, you should use INFORMATION_SCHEMA views, not sysobjects.
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
Of course this assumes you haven't created your own procs named dt_whatever.
(In fact, when I use INFORMATION_SCHEMA.ROUTINES instead of sysobjects, the
dt_ procs are not included, even without the additional WHERE clause. Maybe
this change alone will resolve the situation for you.)
http://www.aspfaq.com/
(Reverse address to reply.)
<recoil@.community.nospam> wrote in message
news:ulSRIWbHFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Awhile back somebody showed me a neat little trick to filter out the
stupid
> dt tables and etc. I am wondering if there is a similar but different
trick
> for procs because I tried running this same query but changed U to P and
it
> did not filter the dt. and other useless sprocs.
>
> @."SELECT name
> FROM sysobjects
> WHERE (xtype = 'U') AND (OBJECTPROPERTY(id, 'IsMSShipped') =
0)
> ORDER BY category DESC, name";
>
>|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.|||Thanks. I will give that a shot when I get time. Are you aware of any
method to get the extra dt tables and dt sprocs "if" i wanted them. or
does the ISMSShipped work AS expected when extracting form
information_schema, because I have another project where I want to get
both I just want them to be under 2 different nodes. One that is for
System and one is for users - for sprocs and tables.