Post reply

List Objects with in a procedure across multiple Database

  • April 27, 2009 at 6:34 am

    #136942

    I need a script which lists all the objects in the stored procedure name given as input.The objects should be listed from other databases also within the server.

    Eg: Consider a procedure(SP1) in Database master using table (Table1) from master and also table or view (Table2) from pubs database.

    The list should give the result as below on giving input of SP1

    Database Name Table Name

    -------------------------------

    Master Table1

    Pubs Table2

  • May 13, 2009 at 6:02 am

    #993598

    Try this:

    declare@querynvarchar(max)

    set@query

    = (

    selectleft(Script.Query, len(Script.Query) - 9)

    from(

    selectScript.Query.query('Query').value('.', 'nvarchar(max)') as Query

    from(

    selectQuery

    = N'

    selectTABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME collate Latin1_General_CI_AS as QualifiedObjectName

    from'

    + sys.databases.[name]

    + '.INFORMATION_SCHEMA.TABLES

    union all'

    fromsys.databases

    for xml path(''), type

    ) Script (Query)

    ) Script

    )

    exec(@query)

    You can work something out, can't you?

    ML

    ---
    Matija Lah, SQL Server MVP
    http://milambda.blogspot.com

  • May 14, 2009 at 5:33 am

    #994386

    Thanks Matija!!

    But this will not fetch the tables within a procedure when the tables used in the procedure are from a different database.Please check my previous post for the requirement.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply