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

Question

Post reply

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_NameColumn_Name
Used_In_SP dbo Stored Procedure Data_TableFamily
Used_In_SP dbo Stored Procedure Data_TableName
Used_In_SP dbo Stored Procedure Data_TableValue
Used_In_TableFunction dbo Function Table Data_TableID
Used_In_TableFunction dbo Function Table Data_TableName
Used_In_TableFunction dbo Function Table Data_TableValue
Used_in_View dbo View Data_Table Family
Used_in_View dbo View Data_TableID
Used_in_View dbo View Data_TableValue

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!