Find all tables and columns used in stored procedure, table function, view and Stored procedure

• ram1447

  SSC-Addicted

  Points: 499

  More actions

  September 28, 2015 at 6:06 am

  #303463

  Hi All,

  I want write query which show all tables and columns used in stored procedure, 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,Type ,Table_Name,Column_Name

  from cte_Required_Query

  object_Name SCHEMA_NAME Type Table_Name Column_Name

  Used_In_SP dbo Stored Procedure Data_Table Family

  Used_In_SP dbo Stored Procedure Data_Table Name

  Used_In_SP dbo Stored Procedure Data_Table Value

  Used_In_TableFunction dbo Function Table Data_Table ID

  Used_In_TableFunction dbo Function Table Data_Table Name

  Used_In_TableFunction dbo Function Table Data_Table Value

  Used_in_View dbo View Data_Table Family

  Used_in_View dbo View Data_Table ID

  Used_in_View dbo View Data_Table Value

• Lowell

  SSC Guru

  Points: 323482

  More actions

  September 28, 2015 at 6:58 am

  #1829848

  DECLARE @ObjectName sysname = 'UsedInView'

  SELECT

  SCHEMA_NAME(so.SCHEMA_ID) AS SchemaName,

  so.name AS ObjectName,

  so.type_desc,

  sed.referenced_server_name,

  sed.referenced_database_name,

  sed.referenced_schema_name,

  sed.referenced_entity_name,*

  FROM sys.sql_expression_dependencies sed

  INNER JOIN sys.objects so

  ON sed.referencing_id = so.OBJECT_ID

  WHERE sed.referenced_entity_name = @ObjectName

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!