Kdtyjb

Has a Nobel Peace laureate ever been accused of war crimes?

`microtype`: Set Minimum Width of a Space

Could moose/elk survive in the Amazon forest?

finding a tangent line to a parabola

Why do distances seem to matter in the Foundation world?

view price of already bought and installed apps on play sotr

How much of a wave function must reside inside event horizon for it to be consumed by the black hole?

I preordered a game on my Xbox while on the home screen of my friend's account. Which of us owns the game?

Why doesn't the standard consider a template constructor as a copy constructor?

What was Apollo 13's "Little Jolt" after MECO?

What *exactly* is electrical current, voltage, and resistance?

What is the best way to deal with NPC-NPC combat?

Can you stand up from being prone using Skirmisher outside of your turn?

All ASCII characters with a given bit count

Air bladders in bat-like skin wings for better lift?

Will I lose my paid in full property

How do I prove this combinatorial identity

Is there metaphorical meaning of "aus der Haft entlassen"?

What is purpose of DB Browser(dbbrowser.aspx) under admin tool?

Unable to completely uninstall Zoom meeting app

Why didn't the Space Shuttle bounce back into space as many times as possible so as to lose a lot of kinetic energy up there?

My admission is revoked after accepting the admission offer

Crossed out red box fitting tightly around image

Why must Chinese maps be obfuscated?

Can a stored procedure reference the database in which it is stored?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

1

Suppose I have a stored procedure that is duplicated, with some modifications, in several databases. And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?

sql-server

share|improve this question

asked 2 hours ago

Jim ClarkJim Clark

1062

New contributor

Jim Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  If it is duplicated in several databases, then it is stored in several databases, so what would be the (seemingly) single path you are looking for? Or am I misunderstanding your question?
  
  – scsimon
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you calling the stored procedure in database A, from database B?
  
  – Anthony Genovese
  2 hours ago
  

  
  
  
  

add a comment | 

1

Suppose I have a stored procedure that is duplicated, with some modifications, in several databases. And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?

sql-server

share|improve this question

asked 2 hours ago

Jim ClarkJim Clark

1062

New contributor

Jim Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  If it is duplicated in several databases, then it is stored in several databases, so what would be the (seemingly) single path you are looking for? Or am I misunderstanding your question?
  
  – scsimon
  2 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you calling the stored procedure in database A, from database B?
  
  – Anthony Genovese
  2 hours ago
  

  
  
  
  

add a comment | 

Suppose I have a stored procedure that is duplicated, with some modifications, in several databases. And I want to reference the database in which the stored procedure is stored, even if it is executed in another database.

Is there a way to retrieve the full path (..) or otherwise retrieve the database in which the stored procedure is stored, rather than the current database?

sql-server

share|improve this question

asked 2 hours ago

Jim ClarkJim Clark

1062

New contributor

Jim Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 2 hours ago

Jim ClarkJim Clark

1062

New contributor

Jim Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 2 hours ago

Jim ClarkJim Clark

1062

New contributor

Jim Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 hours ago

Jim ClarkJim Clark

1062

New contributor

Jim Clark is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 hours ago

Jim ClarkJim Clark

1062

2 Answers
2

active

oldest

votes

2

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure. In particular,

For static SQL in a stored procedure:

• Unqualified object names will resolve relative to the schema containing the stored procedure.




• Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

• Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).




• Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.

share|improve this answer

edited 1 hour ago

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

add a comment | 

1

Here is a quick example that I put together showing common functions used to get close to what you are looking for.

/** Create a procedure in master to demonstrate

    DB_NAME()

    OBJECT_SCHEMA_NAME()

    OBJECT_NAME()

    @@PROCID

**/

USE [master]

GO



CREATE OR ALTER PROCEDURE dbo.uspTestMe

AS

BEGIN



    PRINT 'Database: ' + DB_NAME()

    PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)

    PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)



END



GO



/** CHANGE Context to TempDB

    Execute procedure in master

    **/

USE [tempdb]

GO



EXEC master.dbo.uspTestMe 



GO



/** Cleanup in master **/

USE [master]

GO



DROP PROCEDURE IF EXISTS dbo.uspTestMe 

share|improve this answer

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Jim Clark is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f236675%2fcan-a-stored-procedure-reference-the-database-in-which-it-is-stored%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure. In particular,

For static SQL in a stored procedure:

• Unqualified object names will resolve relative to the schema containing the stored procedure.




• Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

• Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).




• Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.

share|improve this answer

edited 1 hour ago

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

add a comment | 

2

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure. In particular,

For static SQL in a stored procedure:

• Unqualified object names will resolve relative to the schema containing the stored procedure.




• Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

• Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).




• Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.

share|improve this answer

edited 1 hour ago

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

add a comment | 

I want to reference the database in which the stored procedure is stored, even if it is executed [from] another database.

Just use one-part or two-part names in the stored procedure, and it will reference objects in the database containing the stored procedure. In particular,

For static SQL in a stored procedure:

• Unqualified object names will resolve relative to the schema containing the stored procedure.




• Two-part names will resolve relative to the database containing the stored procedure.

For dynamic SQL in a stored procedure:

• Unqualified object names will resolve relative to the default schema of the user identity running the stored procedure (by default, the caller).




• Two-part names will resolve relative to the database containing the stored procedure.

The db_name() function will return the name of the database containing the stored procedure in both cases.

share|improve this answer

edited 1 hour ago

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

share|improve this answer

edited 1 hour ago

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

answered 2 hours ago

David Browne - MicrosoftDavid Browne - Microsoft

12.7k733

1

Here is a quick example that I put together showing common functions used to get close to what you are looking for.

/** Create a procedure in master to demonstrate

    DB_NAME()

    OBJECT_SCHEMA_NAME()

    OBJECT_NAME()

    @@PROCID

**/

USE [master]

GO



CREATE OR ALTER PROCEDURE dbo.uspTestMe

AS

BEGIN



    PRINT 'Database: ' + DB_NAME()

    PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)

    PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)



END



GO



/** CHANGE Context to TempDB

    Execute procedure in master

    **/

USE [tempdb]

GO



EXEC master.dbo.uspTestMe 



GO



/** Cleanup in master **/

USE [master]

GO



DROP PROCEDURE IF EXISTS dbo.uspTestMe 

share|improve this answer

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818

add a comment | 

1

Here is a quick example that I put together showing common functions used to get close to what you are looking for.

/** Create a procedure in master to demonstrate

    DB_NAME()

    OBJECT_SCHEMA_NAME()

    OBJECT_NAME()

    @@PROCID

**/

USE [master]

GO



CREATE OR ALTER PROCEDURE dbo.uspTestMe

AS

BEGIN



    PRINT 'Database: ' + DB_NAME()

    PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)

    PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)



END



GO



/** CHANGE Context to TempDB

    Execute procedure in master

    **/

USE [tempdb]

GO



EXEC master.dbo.uspTestMe 



GO



/** Cleanup in master **/

USE [master]

GO



DROP PROCEDURE IF EXISTS dbo.uspTestMe 

share|improve this answer

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818

add a comment | 

Here is a quick example that I put together showing common functions used to get close to what you are looking for.

/** Create a procedure in master to demonstrate

    DB_NAME()

    OBJECT_SCHEMA_NAME()

    OBJECT_NAME()

    @@PROCID

**/

USE [master]

GO



CREATE OR ALTER PROCEDURE dbo.uspTestMe

AS

BEGIN



    PRINT 'Database: ' + DB_NAME()

    PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)

    PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)



END



GO



/** CHANGE Context to TempDB

    Execute procedure in master

    **/

USE [tempdb]

GO



EXEC master.dbo.uspTestMe 



GO



/** Cleanup in master **/

USE [master]

GO



DROP PROCEDURE IF EXISTS dbo.uspTestMe 

share|improve this answer

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818

/** Create a procedure in master to demonstrate

    DB_NAME()

    OBJECT_SCHEMA_NAME()

    OBJECT_NAME()

    @@PROCID

**/

USE [master]

GO



CREATE OR ALTER PROCEDURE dbo.uspTestMe

AS

BEGIN



    PRINT 'Database: ' + DB_NAME()

    PRINT 'Schema Name: ' + OBJECT_SCHEMA_NAME(@@PROCID)

    PRINT 'Procedure Name: ' + OBJECT_NAME(@@PROCID)



END



GO



/** CHANGE Context to TempDB

    Execute procedure in master

    **/

USE [tempdb]

GO



EXEC master.dbo.uspTestMe 



GO



/** Cleanup in master **/

USE [master]

GO



DROP PROCEDURE IF EXISTS dbo.uspTestMe 

share|improve this answer

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818

answered 2 hours ago

Jonathan FiteJonathan Fite

4,123818