sql objects drill down

See more:

SQL

Hi Everyone,

I need a help in sql.

Below is the details:

from the front end, i will select few sql objects
for eg,
i am selecting
a) ProcedureName1, View_1 from Company_BAT and
b) GetEmpByProjID,GetAllProj, samplefunction from Company_DEV

here
ProcedureName1,GetEmpByProjID,GetAllProj are procedures.
View_1 is a view.
samplefunction is a function.


ProcedureName1 uses a) View_1 (View_1 uses Table_1)
b) Table_1
c) Table_2

View_1 uses a) Table_1

GetEmpByProjID uses a) Employee

GetAllProj uses a) Employee
b) Project

samplefunction a) it doesnt uses any objects

i need the output in the format as in the below screenshot in the below link:

http://vijays301989.blogspot.com/b/post-preview?token=QH4f3z8BAAA.jAJqT5poBN3LnJ_eDbQDgA.vZNULScDlF40PLWlpXSZKA&postId=6529902930179783663&type=POST[^]

PLease advise, how to obtain result in this format.

[EDIT #1 - moved from comment]

CREATE PROCEDURE usp_FindObjectInAllDatabase
(@ObjectName VARCHAR(256),@objectType Varchar(10))
AS
    DECLARE @DBName VARCHAR(256)
    DECLARE @varSQL VARCHAR(512)
    DECLARE @getDBName CURSOR
    SET @getDBName = CURSOR FOR
    SELECT name
    FROM sys.databases
   
    CREATE TABLE #TmpTable (DBName VARCHAR(256),
    SchemaName VARCHAR(256),
    ObjectName VARCHAR(256),ObjectType Varchar(256))
    OPEN @getDBName
    FETCH NEXT
    FROM @getDBName INTO @DBName
   
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @varSQL = 'USE ' + @DBName + ';
        INSERT INTO #TmpTable
        SELECT '''+ @DBName + ''' AS DBName,
        SCHEMA_NAME(schema_id) AS SchemaName,
        name AS ObjectName,Type_Desc as ObjectType
        FROM sys.objects
        WHERE name LIKE ''' + @ObjectName + ''''
        EXEC (@varSQL)
        FETCH NEXT
        FROM @getDBName INTO @DBName
    END
    CLOSE @getDBName
    DEALLOCATE @getDBName
    SELECT *
    FROM #TmpTable
    DROP TABLE #TmpTable
    GO