system stored procedures

It is me again, I just realized that system stored procedures only
shows in master database, but not in my database I created.
Should I have system stored procedures in my databases?
If yes, do I need do anything while I create new databases?

Your help is great appreciated,



iccsi wrote:
> I do not see system stored procedures on Enterprise Manager for my SQL
> Server 2000.
>
>
> Are there any option to see the system stored procedures on the
> enterprise anager on the right pane when I clcik on stored procedure
> on the left pane?
>
>
> Your help is great appreciated,

iccsi (inunghatgmaildotcom) writes:
> It is me again, I just realized that system stored procedures only
> shows in master database, but not in my database I created.
> Should I have system stored procedures in my databases?

No, the system procedure exists in master only. (On SQL 2000. On
later versions, the system procedures resides in a hidden database.)

You can however, execute a system procedure in any database, and it
will act on objects in that database.


--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks millions for helping,

Erland Sommarskog wrote:
> iccsi (inunghatgmaildotcom) writes:
> > It is me again, I just realized that system stored procedures only
> > shows in master database, but not in my database I created.
> > Should I have system stored procedures in my databases?
>
> No, the system procedure exists in master only. (On SQL 2000. On
> later versions, the system procedures resides in a hidden database.)
>
> You can however, execute a system procedure in any database, and it
> will act on objects in that database.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Thu, 07 Apr 2011 23:26:34 +0200, Erland Sommarskog
wrote:

>iccsi (inunghatgmaildotcom) writes:
>> It is me again, I just realized that system stored procedures only
>> shows in master database, but not in my database I created.
>> Should I have system stored procedures in my databases?
>
>No, the system procedure exists in master only. (On SQL 2000. On
>later versions, the system procedures resides in a hidden database.)
>
>You can however, execute a system procedure in any database, and it
>will act on objects in that database.

Then what is this?

In the Object Browser, expand Databases -- database> -- Programmability -- Stored Procedures -- System Stored
Procedures? It is quite a long list.

Or do you mean that the system stored procedures are *in* master,
but show *as if in* every database?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genewatocisdotnet) writes:
> Then what is this?
>
> In the Object Browser, expand Databases -- > database> -- Programmability -- Stored Procedures -- System Stored
> Procedures? It is quite a long list.
>
> Or do you mean that the system stored procedures are *in* master,
> but show *as if in* every database?

I meant exactly what I said. I was answering a question about SQL 2000.

From SQL 2005, the system proceudres are in the database
mssqlsystemresource, a hidden readonly database, which from a logical point
of view is akin to a DLL. And in SQL 2005 and onwards, the catalog views
do indeed list the system procedures in all database.


--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx