SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How we can determine table column dependencies within a SQL database?

I came a situation during some analysis where i had to figure out all dependency of a column across database. This also required when we do some changes on table columns and wanted to know the impact of such changes. One way is that  we can use SYSCOMMENTS table.  It is a table which contains entries for each views, rules, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The column TEXT in the syscomments table contains the actual code for all these objects, and knowing it you can write a code to check the dependencies. I am explaining different approach.





/* Table Name for which we need to find the dependency of columns.*/
Declare @TableNamevarchar(max)='tblSupplierRequestOrder'
/* Column name for which we need to find the dependnecy in a database*/
Declare @ColumnNamevarchar(max)='OrderDate'

select  OBJECT_NAME(k.parent_object_id) as parentTableName
          , c1.name as parentColumn
          , OBJECT_NAME(k.referenced_object_id)as referencedTableName
          , c2.name as referencedColumn
    from    sys.foreign_keys k
            innerjoin sys.foreign_key_columnsf
              on  f.parent_object_id = k.parent_object_id
            innerjoin sys.columns c1
              on  c1.column_id = f.parent_column_id
              andc1.object_id = k.parent_object_id
            innerjoin sys.columns c2
              on  c2.column_id = f.referenced_column_id
              andc2.object_id = k.referenced_object_id
    where   c2.name = @ColumnName
    and     OBJECT_NAME(k.referenced_object_id)=@TableName




TSQL Circle Live

Arun Kumar has over 8 years' extensive working experience in database query development, design, supporting many different versions of MS-SQL. He has worked to develop efficient reports, index optimization for complex queries, and has finance and operation domain experience. He has also worked with SPLUNK and enjoys training technical and non-technical colleagues.

Comments

Leave a comment on the original post [tsqlcirclelive.blogspot.com, opens in a new window]

Loading comments...