locked
Find all tables and columns used in table function, view and Stored procedure RRS feed

  • Question

  • Hi All,

    I want write query which show all tables and columns used in table function, view and Stored procedure

     

     

    /* Create Table  */

    CREATE TABLE [dbo].[Data_Table](

           [ID] [int] NOT NULL,

           [Name] [varchar](50) NULL,

           [Family] [varchar](50) NULL,

           [Value] [int] NULL,

    ) ON [PRIMARY]

     

    /*  Used my Table in View so that used fields ID,Family with alias , Value */

    create VIEW [dbo].[Used_in_View]

    AS

    SELECT        ID, Family AS LastName, Value

    FROM            dbo.Data_Table

     

    /*  Used my Table in Table Function so that used fields ID,Name with alias , Value */

    CREATE FUNCTION Used_In_TableFunction

    (     

    )

    RETURNS TABLE

    AS

    RETURN

    (

           SELECT        ID, Name AS FirstName, Value

           FROM            dbo.Data_Table

    )

     

    /*   Used my Table in Stored Procedure so that used fields Name & Family with alias , Value  with alias  */

     

    CREATE PROCEDURE Used_In_SP

    AS

    BEGIN

           SELECT        Name + ' ' + Family Full_Name , Value Amunt

           FROM            dbo.Data_Table

    END

    GO

     

     

    Output query Sample:

    with cte_Required_Query as (

     

                                                    ....

     

     

                                                    )

    select object_Name,SCHEMA_NAME,Table_Name,Column_Name

    from cte_Required_Query

     

    object_Name

    SCHEMA_NAME

    Table_Name

    Column_Name

    Used_In_SP

    dbo

    Data_Table

    Family

    Used_In_SP

    dbo

    Data_Table

    Name

    Used_In_SP

    dbo

    Data_Table

    Value

    Used_In_TableFunction

    dbo

    Data_Table

    ID

    Used_In_TableFunction

    dbo

    Data_Table

    Name

    Used_In_TableFunction

    dbo

    Data_Table

    Value

    Used_in_View

    dbo

    Data_Table

    Family

    Used_in_View

    dbo

    Data_Table

    ID

    Used_in_View

    dbo

    Data_Table

    Value

    Monday, September 28, 2015 12:46 PM

Answers