sql-servert-sqlstored-proceduressql-server-2012cursors

How to find the name of the database from the given table name, using stored procedure?

I am newbie here. I have many databases in my SSMS, so I need to find the database name using the given table name using stored procedures. And I am not good at writing SP's and handling errors. I apologize for my English.

Thank you

I tried it using cursors in stored procedure. But I am getting errors as I am not good at handling errors.

Solution

  • You could create the stored procedure in the following:

    CREATE PROCEDURE sp_Get_Tables
    @schema VARCHAR(50) = 'dbo',
    @table_name VARCHAR(100) = 'Default_Table_Name'
AS
SELECT name 
FROM sys.databases 
WHERE CASE WHEN state_desc = 'ONLINE' THEN OBJECT_ID(QUOTENAME(name) + '.' + @schema + '.' + @table_name, 'U') END IS NOT NULL

    And execute the stored procedure you can in the following:

    EXEC sp_Get_Names 'Schema', 'Table_Name'