Direct dependencies on a column...

I came across a question recently about discovering the constraints that are dependent on a column like CHECK constraint, defaults and so on. In trying to solve that problem, I came across few interesting solutions that uses some new relational features and the catalog views available in SQL Server 2005. I will share those here. There is a wealth of information exposed via the catalog views in SQL Server 2005 and be sure to check that out in the Books Online.

 

Let's now look at the problem. Given the schema objects below, I want to identity all the direct dependencies on the column "t.c".

 

use tempdb;
go
create table t (
c char(1) not null
constraint df_t_c default( '' )
constraint ck_t_c check( c > '' )
constraint pk_t_c primary key
constraint fk_t_c references t (c),
c1 as c
);

create unique nonclustered index ix_t_c on t( c );
go
create view vt with schemabinding as select c from dbo.t;
go

The direct dependencies in this sample schema include the following: check constraint, default constraint, primary key, foreign key, computed column, index and the view. Before jumping into the solution, there are legitimate cases where you may want to get such dependencies - for reporting or modifying schema elements.

 

Here is the query to get only the dependent constraints for the column:

 

with constraint_depends
as
(
select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.CONSTRAINT_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as c
union all
select s.name, o.name, c.name, d.name
from sys.default_constraints as d
join sys.objects as o
on o.object_id = d.parent_object_id
join sys.columns as c
on c.object_id = o.object_id and c.column_id = d.parent_column_id
join sys.schemas as s
on s.schema_id = o.schema_id
)
select c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.CONSTRAINT_NAME
from constraint_depends as c
where c.TABLE_NAME = 't' and c.COLUMN_NAME = 'c';
go

/*

TABLE_SCHEMA TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
dbo t c fk_t_c
dbo t c pk_t_c
dbo t c ck_t_c
dbo t c df_t_c

*/

 

This query uses the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view to get information about all constraints except defaults (since standard doesn't include default as constraint the view doesn't expose that). And the defaults are obtained using the sys.default_constraints catalog view. The information in the query can be used to say drop the constraints before dropping the column from a script.

 

Now, the next query uses only the new catalog views to obtain all the required information.

with rel_objs (obj_name, type_name, type_desc, parent_object_id, parent_column_id)
as
(
select d.name, d.type, d.type_desc, d.parent_object_id, d.parent_column_id
from sys.default_constraints as d -- defaults
union all
select c.name, c.type, c.type_desc, c.parent_object_id, c.parent_column_id
from sys.check_constraints as c -- check constraints
union all
select k.name, k.type, k.type_desc, k.parent_object_id, kc.column_id
from sys.key_constraints as k -- primary key and unique constraints
join sys.index_columns as kc
on kc.object_id = k.parent_object_id and kc.index_id = k.unique_index_id
union all
select f.name, f.type, f.type_desc, f.parent_object_id, fc.parent_column_id
from sys.foreign_keys as f -- foreign key constraints
join sys.foreign_key_columns as fc
on fc.constraint_object_id = f.object_id and fc.parent_object_id = f.parent_object_id
union all
select c.name, 'CC', 'COMPUTED_COLUMN', d.referenced_major_id, d.referenced_minor_id
from sys.sql_dependencies as d -- computed columns
join sys.columns as c
on c.object_id = d.object_id and c.column_id = d.column_id
where d.object_id = d.referenced_major_id and d.referenced_minor_id > 0 and d.column_id > 0
union all
select o.name, o.type, o.type_desc, d.referenced_major_id, d.referenced_minor_id
from sys.sql_dependencies as d -- views
join sys.objects as o
on o.object_id = d.object_id
where d.object_id <> d.referenced_major_id and d.referenced_minor_id > 0 and o.type = 'V'
union all
select i.name, 'IX', i.type_desc, i.object_id, ic.column_id
from sys.indexes as i -- indexes
join sys.index_columns as ic
on ic.index_id = i.index_id and ic.object_id = i.object_id
where i.is_primary_key = 0 and i.is_unique_constraint = 0
),
rel_objs_det (schema_name, table_name, column_name, rel_obj_name, rel_type_name, rel_type_desc)
as (
select s.name, o.name, c.name, r.obj_name, r.type_name, r.type_desc
from rel_objs as r -- names for the table/column to query for:
join sys.objects as o
on r.parent_object_id = o.object_id
join sys.columns as c
on c.object_id = o.object_id and c.column_id = r.parent_column_id
join sys.schemas as s
on s.schema_id = o.schema_id
)
select r.schema_name, r.table_name, r.column_name, r.rel_obj_name, r.rel_type_name, r.rel_type_desc
from rel_objs_det as r
where r.table_name = 't' and r.column_name = 'c'; /* MODIFY or REMOVE where clause if you want run this query for other tables. */
go

 

/*

schema_name table_name column_name rel_obj_name rel_type_name rel_type_desc
dbo t c df_t_c D DEFAULT_CONSTRAINT
dbo t c ck_t_c C CHECK_CONSTRAINT
dbo t c pk_t_c PK PRIMARY_KEY_CONSTRAINT
dbo t c fk_t_c F FOREIGN_KEY_CONSTRAINT
dbo t c c1 CC COMPUTED_COLUMN
dbo t c vt V VIEW
dbo t c ix_t_c IX NONCLUSTERED

*/

 

This query highlights the use of several catalog views: default_constraints, check_constraints, key_constraints, foreign_keys, foreign_key_columns, sql_dependencies, indexes and index_columns. Note that some of the type values that are displayed in the query is not derived from the catalog views they are just constants I came up with for the display purpose. This solution also shows how to use a Common-Table Expression (CTE) to simplify complex queries. This query can be put in a view and used.

 

Lastly, the solution is by no means complete. There are other objects that can depend on the column like full-text indexes, table-valued functions, chain of dependencies if the view on the column is referenced by another for example and so on. I will leave those as exercise for users to try and implement.

 

--

Umachandar Jayachandran