SQL SERVER - MAP PROCEDURES AND FUNCTIONS COLUMNS AND TABLES

mapsqlserver.sql

-- 23/12/2021 - Bruno
drop table if exists #resouce_mapping ;
select distinct
	s.name as schema_name
	,o.type_desc as resource_type
	,p.name as resource_name
	,t.name as table_name
	,c.name	as column_name
	,ty.name as type
	,c.max_length
	,c.precision
	,c.scale
into #resouce_mapping
from sys.sql_expression_dependencies ed
inner join sys.procedures p					on p.object_id = ed.referencing_id
inner join sys.tables t						on t.object_id = ed.referenced_id
inner join sys.columns c					on t.object_id = c.object_id
inner join sys.types ty						on c.system_type_id = ty.system_type_id
inner join sys.schemas s					on s.schema_id = p.schema_id
inner join sys.objects o					on o.object_id = p.object_id

insert into #resouce_mapping
select distinct 
	s.name as schema_name
	,o.type_desc as resource_type
	,o.name as function_name
	,t.name as table_name
	,c.name	as column_name
	,ty.name as type
	,c.max_length
	,c.precision
	,c.scale
from sys.sql_expression_dependencies ed
inner join sys.objects o					on ed.referencing_id = o.object_id
inner join sys.tables t						on t.object_id = ed.referenced_id
inner join sys.columns c					on t.object_id = c.object_id
inner join sys.types ty						on c.system_type_id = ty.system_type_id
inner join sys.schemas s					on s.schema_id = o.schema_id

where 
	o.type_desc in(
				'TYPE_TABLE'
			,	'SQL_TABLE_VALUED_FUNCTION'
			,	'SQL_SCALAR_FUNCTION'
			,	'VIEW'
		)

select * from #resouce_mapping