Stored procedure scope: using sp from another database

Question

I stored a procedure called PROC_fetchTableInfo (a simple selection from sys.tables) in a database called

Cust

I would like to use this procedure in another database named

Dept

I tried to execute this sp in this database using the command

EXECUTE Cust.dbo.PROC_fetchTableInfo

but as a result, I get tables from the Cust database. How to make this procedure work in the Dept database?

Answer 1

. Cust.dbo.PROC_fetchTable T, T dbo Cust. , .

, , , : Dept.dbo.PROC_fetxTableInfo. , Dynamic-SQL. DRY , T-SQL - , , C/# mind . Dept.

Answer 2

, Cust.dbo.PROC_fetchTableInfo, Cust. , Dept.

( ) , , sproc.

, "Common" EXEC Common..PROC_fetchTableInfo @databaseName='Dept'

Answer 3

( , ). , sp_ .

use master

go

CREATE PROCEDURE dbo.sp_sys_tables_select
AS
SELECT * FROM sys.tables 

GO

EXEC sys.sp_MS_marksystemobject 'dbo.sp_sys_tables_select'
GO


EXEC tempdb.dbo.sp_sys_tables_select

EXEC msdb.dbo.sp_sys_tables_select