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.

Getting Return Value from system SPROC

I'm trying to learn how to write/use system sprocs for the 1st time. SYSTEM
SPROC below is a working sproc I have in the master db that returns a 1 if a
file exists and 0 if it doesn't. If I run QA CODE 1, my SYSTEM SPROC returns
the correct 1 or 0.
However, if I use my sproc as in QA CODE 2 to act on the returned value, I
get syntax errors.
How can I write QA CODE 2 so I can use an IF statement to test and act on
the returned value of my SYSTEM SPROC?
-- SYSTEM SPROC ************
CREATE procedure [dbo].[usp_FileExists]
@.physname nvarchar(260)
as
SET nocount ON
DECLARE @.i int
EXEC master..xp_fileexist @.physname,@.i out
SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END
-- QA CODE 1 ****************
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
-- QA CODE 2 ****************
IF EXEC master.dbo.usp_FileExists @.physname =
'D:\data\sql_data\myData_Data.MDF' = 0
PRINT 'Does not Exist 'You cannot directly use a SProc inside an if condition.
If you really want to call within the if condition, then create a function.
But remember that the function works because its an extended proc.
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF'
,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '
But again, unless you have a pressing reason to call it within the if
condition, I would suggest you call the proc the same way as the extended
proc is called within it..
Like this..
DECLARE @.RC int
DECLARE @.physname nvarchar(260)
set @.physname = 'D:\data\sql_data\myData_Data.MDF'
EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
if(@.rc = 0)
PRINT 'Does not Exist '
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||I need a little more explanation. I tried your code on my "System Sproc" and
it just returns the correct 1 or 0, not the PRINT message. So why would the
last IF test be ignored?
Should my "System Sproc" be an extended system Sproc?
If you know of any books that teach system and extended sprocs please let me
know. I'd really like to learn things like when I have to use a function vs.
sproc.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
> You cannot directly use a SProc inside an if condition.
> If you really want to call within the if condition, then create a
> function.
> But remember that the function works because its an extended proc.
> CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
> returns int
> as
> begin
> DECLARE @.i int
> EXEC master.dbo.usp_FileExists @.physname =
> 'z:\data\sql_data\myData_Data.MDF'
> ,@.i out
> return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
> end
> IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
> PRINT 'Does not Exist '
> But again, unless you have a pressing reason to call it within the if
> condition, I would suggest you call the proc the same way as the extended
> proc is called within it..
> Like this..
> DECLARE @.RC int
> DECLARE @.physname nvarchar(260)
> set @.physname = 'D:\data\sql_data\myData_Data.MDF'
> EXEC @.RC = [master].[dbo].[usp_FileExists1] @.physname
> if(@.rc = 0)
> PRINT 'Does not Exist '
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||You do NOT want a 'extended' stored procedure. That is a different animal to
tally.
---
Here is a slightly revised version of the FileExists function (master..xp_Fi
leExist is an 'UnDocumented' system function. There is no guarantee it will
be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be co
nsistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx
.gbl...
>I need a little more explanation. I tried your code on my "System Sproc" an
d
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
> Should my "System Sproc" be an extended system Sproc?
>
> If you know of any books that teach system and extended sprocs please let
me
> know. I'd really like to learn things like when I have to use a function v
s.
> sproc.
>
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>
>|||thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OeixErImGHA.4696@.TK2MSFTNGP05.phx.gbl...
You do NOT want a 'extended' stored procedure. That is a different animal
totally.
---
Here is a slightly revised version of the FileExists function
(master..xp_FileExist is an 'UnDocumented' system function. There is no
guarantee it will be in future versions.)
CREATE FUNCTION dbo.fnFileExists
( @.FileName nvarchar(260) )
RETURNS int
AS
BEGIN
DECLARE @.FileFound int
EXECUTE master..xp_FileExist @.FileName, @.FileFound OUT
RETURN( SELECT CASE WHEN @.FileFound = 1 THEN 0 ELSE 1 END )
END
GO
-- Use it like this:
IF dbo.fnFileExists( 'd:\temp\ISASettings.txt' ) = 0
PRINT 'File found'
ELSE
PRINT 'File not found'
I change the return values to 0=True, 1= False. This is in keeping with SQL
return 0=No error, not zero = error. You may perfer the opposite. Just be
consistent.
---
Entry level Books: (Written before SQL 2005 -but still useful.)
SQL server 2000 Stored Procedure Handbook (Paperback)
by Tony Bain, Robin Dewson, Chuck Hawkins, Louis Davidson
ISBN: 1861008252
Writing Stored Procedures for Microsoft SQL Server
Sams Publishing
ISBN: 0-672-31886-5
A little more advanced -yet excellent:
A Developer's Guide to SQL Server 2005
Bob Beuchemin, Dan Sullivan
ISBN: 0321382188
Programming Microsoft SQL ServerT 2005
Andrew J. Brust; Stephen Forte
ISBN 0-7356-1923-9
(And there are several new (SQL 2005) books on more advance topices related
to programming, queries, etc.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"scott" <sbailey@.mileslumber.com> wrote in message
news:%23Q7KqzHmGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I need a little more explanation. I tried your code on my "System Sproc"
>and
> it just returns the correct 1 or 0, not the PRINT message. So why would
> the
> last IF test be ignored?
> Should my "System Sproc" be an extended system Sproc?
> If you know of any books that teach system and extended sprocs please let
> me
> know. I'd really like to learn things like when I have to use a function
> vs.
> sproc.
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:69AD57EA-DBF8-4839-BEC2-0A1DAD7974C8@.microsoft.com...
>|||scott wrote:
> I need a little more explanation. I tried your code on my "System Sproc" a
nd
> it just returns the correct 1 or 0, not the PRINT message. So why would th
e
> last IF test be ignored?
>
I think you ran all of the code together, so that the print statement is
inside the function. Try this instead:
CREATE function [dbo].[usp_FileExists] (@.physname nvarchar(260))
returns int
as
begin
DECLARE @.i int
EXEC master.dbo.usp_FileExists @.physname =
'z:\data\sql_data\myData_Data.MDF' ,@.i out
return(SELECT CASE WHEN @.i=1 THEN 1 ELSE 0 END)
end
Run that, then run this:
IF (master.dbo. usp_FileExists('D:\data\sql_data\myData_
Data.MDF') = 0)
PRINT 'Does not Exist '

> Should my "System Sproc" be an extended system Sproc?
>
NO!!!

Getting proc name from within .Net code

Hi,

I'm currently in the midst of writing my first sqlclr sproc.

In our normal T-SQL sprocs we use the following:

OBJECT_NAME(@.@.PROCID)

to get the name of the sproc. We later use the result of this as a parameter to a custom message when we use RAISERROR.

Question is, is there a way of doing the same from within a SQLCLR sproc? i.e. Can someone give me a bit of code that will return the name of the current sproc? (Obviously this is not the same as the name of the .Net class that implements my sproc).

Thanks in advance for any help that you can provide.

Regards

I've had word from Umachander at MSFT who says that this isn't possible but that they are looking at it for a future version.

-Jamie