Sunday, 14 July 2013

Get the Database name,schema name of the specified object

Here is a Procedure to get database details of the given object.

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

sql objects drill down....