How to find objects which depend on particular column of table.
Ex:
Table: SomeTable
Cols: col1 pk, col2, col3
I want to find all the objects which are dependent on col1 (Pk)
To filter, search for the value in the c1.name or c2.name column. To look for all the references to a certain column, use the c2.name for the column name and the OBJECT_NAME(k. referenced_object_id) as the table which holds the c2 column :) Good Luck!
Using SQL Server Management Studio In Object Explorer, expand Databases, expand a database, and then expand Tables. Right-click a table, and then click View Dependencies.
Using SQL Server Management StudioExpand Stored Procedures, right-click the procedure and then click View Dependencies. View the list of objects that depend on the procedure. View the list of objects on which the procedure depends. Click OK.
A dependency is created between two objects when one object appears by name inside a SQL statement stored in another object. The object which is appearing inside the SQL expression is known as referenced entity and the object which has the SQL expression is known as a referencing entity.
@NoFuchsGavin's script usually works great but has some limitations due to issues with sysdepends
(see this blog post by Pinal Dave for an example where this gives incorrect results).
Microsoft also suggest that you avoid using sysdepends
in new development work.
We can therefore use sys.dm_sql_referencing_entities
and sys.dm_sql_referenced_entities
as suggested here.
However I've noticed that this sometimes excludes column references due to referenced_minor_name
being NULL. I've therefore added another condition which can introduce false positives but ensures that column references are not omitted from the result set.
DECLARE @SchemaName sysname = '{0}';
DECLARE @TableName sysname = '{1}';
DECLARE @ColumnName sysname = '{2}';
SELECT
@SchemaName + '.' + @TableName AS [USED_OBJECT],
@ColumnName AS [COLUMN],
referencing.referencing_schema_name + '.' + referencing_entity_name AS USAGE_OBJECT,
CASE so.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'SP' THEN 'Security policy'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END AS USAGE_OBJECTTYPE,
so.[type] AS USAGE_OBJECTTYPEID
FROM sys.dm_sql_referencing_entities
(
@SchemaName + '.' + @TableName,
'object'
) referencing
INNER JOIN sys.objects so
ON referencing.referencing_id = so.object_id
WHERE
EXISTS
(
SELECT
*
FROM
sys.dm_sql_referenced_entities
(
referencing_schema_name + '.' + referencing_entity_name,
'object'
) referenced
WHERE
referenced_entity_name = @TableName
AND
(
referenced.referenced_minor_name LIKE @ColumnName
-- referenced_minor_name is sometimes NULL
-- therefore add below condition (can introduce False Positives)
OR
(
referenced.referenced_minor_name IS NULL
AND
OBJECT_DEFINITION
(
OBJECT_ID(referencing_schema_name + '.' + referencing_entity_name)
) LIKE '%' + @ColumnName + '%'
)
)
)
ORDER BY
USAGE_OBJECTTYPE,
USAGE_OBJECT
Above script is based on @NoFuchsGavin's answer and this blog post.
I'm interested to know if anyone has managed to find a better way which does not introduce false negatives or positives.
Try this query, it will get you some results that i think you are looking for.
To filter, search for the value in the c1.name or c2.name column.
To look for all the references to a certain column, use the c2.name for the column name and the OBJECT_NAME(k.referenced_object_id) as the table which holds the c2 column :)
Good Luck!
select OBJECT_NAME(k.parent_object_id) as parentTable
, c1.name as parentColumn
, OBJECT_NAME(k.referenced_object_id) as referencedTable
, c2.name as referencedColumn
from sys.foreign_keys k
inner join sys.foreign_key_columns f
on f.parent_object_id = k.parent_object_id
and f.constraint_object_id = k.object_id
inner join sys.columns c1
on c1.column_id = f.parent_column_id
and c1.object_id = k.parent_object_id
inner join sys.columns c2
on c2.column_id = f.referenced_column_id
and c2.object_id = k.referenced_object_id
where c2.name = 'Column'
and OBJECT_NAME(k.referenced_object_id) = 'Table'
This should work!
-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'ColumnToBeSearched' + '%'
Order by OBJECT_NAME(OBJECT_ID)
GO
-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'ColumnToBeSearched' + '%'
GO
Simply Replace {0} and {1}!
declare @tbl_nme as varchar(50)
declare @col_nme as varchar(50)
declare @level int
set @level = 1
set @tbl_nme= '{0}' --TableName
set @col_nme= '{1}' --ColumnName
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, depobj.type as dep_obj_typeID
, @level as level
into #temp
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where obj.name = @tbl_nme
and col.name = @col_nme
while (@@rowcount > 0)
begin
set @level = @level + 1
insert into #temp
select
obj.name as obj_nm
, col.name as col_nm
, depobj.name as dep_obj_nm
, CASE depobj.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END as dep_obj_type
, null as dep_col_nm
, depobj.type as dep_obj_typeID
, @level as level
from sysobjects obj
join syscolumns col on obj.id = col.id
left join (sysdepends dep join sysobjects depobj on depobj.id = dep.id)
on obj.id = dep.depid
and col.colid = dep.depnumber
where exists(select 1 from #temp a where obj.name = a.dep_obj_nm and
col.name = a.dep_col_nm and level = @level - 1 and dep_col_nm is not null)
end
select
obj_nm AS 'TABLE',
col_nm AS 'COLUMN',
dep_obj_nm AS 'USAGE_OBJECT',
dep_obj_type AS 'USAGE_OBJECTTYPE',
dep_obj_typeID AS 'USAGE_OBJECTTYPEID'
from #temp
drop table #temp
The sql provided in the accepted answer above should include an additional join condition between sys.foreign_keys and sys.foreign_key_columns. See line beginning with "and" below:
from sys.foreign_keys k
inner join sys.foreign_key_columns f
on f.parent_object_id = k.parent_object_id
and f.constraint_object_id = k.object_id
For reference, here's the whole script with the amended join:
select OBJECT_NAME(k.parent_object_id) as parentTable
, c1.name as parentColumn
, OBJECT_NAME(k.referenced_object_id) as referencedTable
, c2.name as referencedColumn
from sys.foreign_keys k
inner join sys.foreign_key_columns f
on f.parent_object_id = k.parent_object_id
and f.constraint_object_id = k.object_id
inner join sys.columns c1
on c1.column_id = f.parent_column_id
and c1.object_id = k.parent_object_id
inner join sys.columns c2
on c2.column_id = f.referenced_column_id
and c2.object_id = k.referenced_object_id
where c2.name = 'GUID'
and OBJECT_NAME(k.referenced_object_id) = 'AuthDomain'
Try This: This will give all the object names which are referencing Pk of your table.
select OBJECT_NAME(parent_object_id) from sys.foreign_keys where referenced_object_id = OBJECT_ID('YourTableName')
Find specific column dependencies
SELECT OBJECT_NAME(referencing_id), referenced_database_name, referenced_schema_name, referenced_entity_name FROM sys.sql_expression_dependencies WHERE OBJECT_NAME(referenced_id) = 'table_name' AND OBJECT_DEFINITION(referencing_id) LIKE '%field_name%';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With