Sometimes, we need to modify column values in a table. But it is very much possible that a column is being used by other tables in the database. In such cases we need to ensure that our change in one table does not impact the others.
For example, we might need to rename a particular column (say, Empid) in a table. But if that column is being used in other tables, it will have some impact.
SELECT SCHEMA_NAME(schema_id) AS schema_name,
t.name AS table_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
ORDER BY schema_name, table_name
Above query will return the list of all tables in Mydb database which contains the column Empid. We can use LIKE operator in place of = if we need column names which are similar to that of Empid.
WHERE c.name LIKE '%Empid%'
Query here will list all stored procedures which use the given column name.