Ttyjukl

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

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

Putting Ant-Man on house arrest

Is there really no use for MD5 anymore?

English or Hindi translation of Vyasa Smriti

Unable to completely uninstall Zoom meeting app

How to find if a column is referenced in a computed column?

How would this chord from "Rocket Man" be analyzed?

Should the Product Owner dictate what info the UI needs to display?

The weakest link

Contradiction proof for inequality of P and NP?

How long after the last departure shall the airport stay open for an emergency return?

Obeylines and gappto from etoolbox

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

How exactly does Hawking radiation decrease the mass of black holes?

Bayes factor vs P value

std::unique_ptr of base class holding reference of derived class does not show warning in gcc compiler while naked pointer shows it. Why?

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?

Retract an already submitted recommendation letter (written for an undergrad student)

How do I prove this combinatorial identity

Drawing a german abacus as in the books of Adam Ries

What makes accurate emulation of old systems a difficult task?

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

Did the Roman Empire have penal colonies?

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