Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Creating a System Stored Procedure Expand / Collapse
Posted Friday, May 21, 2004 2:43 PM


Group: General Forum Members
Last Login: Monday, July 17, 2006 8:21 PM
Points: 21, Visits: 1


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

Thank you very much.

Post #117156
Posted Saturday, January 16, 2010 6:08 PM



Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 42,081, Visits: 39,473
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #848794
Posted Tuesday, April 27, 2010 10:46 AM

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: 2 days ago @ 3:10 PM
Points: 662, Visits: 2,926

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

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

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
Post #911292
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse