1

I'm dealing with legacy database. For example, I have table CarTable with definition like this.

CREATE TABLE [dbo].[CarTable] (
    [CarID] BIGINT IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (MAX) NOT NULL,
    [Status] INT NULL,
    [CarStatus] INT NULL,
);

I want to automatically check if any stored procedure or view references column Status. Is it possible? There are many columns named Status in database and trivial approaches like generating database schema dump and greping won't work.

Ginden
  • 5,149
  • 34
  • 68
  • possible duplicate of [Find Column dependency](http://stackoverflow.com/questions/11769172/find-column-dependency) – Patrick Hofman Feb 03 '15 at 09:45
  • 1
    search for: _sys.sql_expression_dependencies_ – Vishal Gajjar Feb 03 '15 at 09:48
  • @PatrickHofman: That answer does not take Stored Procedures and views into account. Only foreign key references to columns. – Dan Feb 03 '15 at 10:00
  • @VishalGajjar: That DMV does not take non-schema-bound columns into account (views are rarely schema bound and stored procedures are never schema bound). One should use sys.dm_sql_referenced_entities instead - check out my answer below. – Dan Feb 03 '15 at 10:02
  • @Dan BOL says it does, I have not used it extensively to confirm.. https://msdn.microsoft.com/en-us/library/bb677315.aspx – Vishal Gajjar Feb 03 '15 at 10:10
  • @VishalGajjar: BOL says that column-level dependencies are only reported for schema-bound entities. 4th bullet from the top. – Dan Feb 03 '15 at 10:11

2 Answers2

1

You can do this from Microsoft SQL Server Management Studio. Right click the Table (in Object Explorer) and select "View Dependencies".

Chris Walsh
  • 3,423
  • 2
  • 42
  • 62
1

Maybe you can use the sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities to do what you want.

For example, this query gives you all objects that (directly) reference the Status column of the CarTable table:

 select referencing_schema_name, referencing_entity_name
 from 
     sys.dm_sql_referencing_entities('dbo.CarTable', 'OBJECT')
 cross apply
     sys.dm_sql_referenced_entities(referencing_schema_name + '.' + referencing_entity_name, 'OBJECT')
 where referenced_minor_name = 'Status'

Check out the documentation of these dmv's for more info.

Dan
  • 10,480
  • 23
  • 49