Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a System Stored Procedure


Creating a System Stored Procedure

Author
Message
Jim Underwood
Jim Underwood
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 1

Calvin,

This is perfect!!!! Exactly what I was looking for.

Thank you very much.


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51929 Visits: 40309
Ok... let's ask the next logical question...

Let's say that I DON'T want to use Master for anything but I would like to have a central repository of common code for use across any and all databases much like you can do with Master. For example,

In Database "A", I have a table called "TableA". In Database "B", I have a stored proc called "ReadTableA". While Database "A" is the current database, I want to execute the "ReadTableA" stored procedure which is located in Database "B" and still have it use "TableA" from Database "A".

Does anyone know how that can be done without the use of dynamic SQL? I've tried synonyms and can execute "ReadTableA" from Database "A", but it expects "TableA" to be in Database "B" where "ReadTableA" lives.

It would be real handy for this to work... we're building multiple client databases and I'd like all of the code to be centralized without having to put it in Master.

Personally, I don't believe it can be done without dynamic SQL but I thought I'd ask.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Phil Factor
Phil Factor
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 2953

Oh dear. This article really should have explained more. As it stands, I'm afraid it is rather misleading. Although it is right in saying that many such procedures will work in some ways as system stored procedures just by putting them in the Master database and by prefixing them with 'sp_', there are differences (which vary from version to version). One important one is that until the sp has been registered as a system stored procedure, it won't be able to see the Object Catalog views for the database, only the master ones. The only reason that the example works is that it uses the old system tables, which for compatibility reasons can be seen. Unfortunately, anyone who takes the advice in this article will become very frustrated. Only if a stored procedure is flagged as being MS_Shipped will it behave in every way as the author suggests. The code in the article will work, but it will not if you use the Object Catalog Views. This won't work. ...


create PROCEDURE sp_FindTableNames
(
@ColumnName varchar(128)
) AS

SELECT *
FROM sys.objects
WHERE object_id IN
(
SELECT object_id
FROM sys.columns
WHERE name = @ColumnName
)
AND type = 'U'
go



You can get around this by registering the stored procedure but there is no way of undoing it. You have to delete and recreate if you need to alter it. Also, this stored procedure is officially 'undocumented' though well known, so you use it at your peril and (all the usual caveats).




if not exists (SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'sp_FindTableNames' and IS_MS_SHIPPED=1)
EXEC sp_ms_marksystemobject 'sp_FindTableNames'



My advice is that it is best not to create a system stored procedure unless you fully understand the repercussions, and have the necessary permi9ssions. I agree it is very magical to have your own special versions of SP_Who, and SP_Help, but please use caution. It is probably best to have a copy of your stored procedure in the database that you are developing, though I agree that this entails a lot more housekeeping and ls likely to leave unwanted scaffolding on your nice new database, if you don't tidy up.




Best wishes,

Phil Factor
Simple Talk
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search