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

Comments

• Anonymous
  September 05, 2005
  Hi. I like your article very much. I need to get the name of the default constraint and tried to use sys.defafault_comstraints but it is not recocniced on my server. Is it because I have the SQL2000 server and not the 2005?
  Jorgen

• Anonymous
  September 06, 2005
  Good stuff, but the constraint info was missing one key type that I have been trying to reveal from system tables. Defualts created (Create Default - sys.objects as D where parent_object_id = 0) are not stored in sys.default_constraints - this is fine except that drom which system table do I retrieve the default value? Example: create default load_date as getdate() load_date became a row in sys.objects, but I cannot find the system table where its default value is revealed.

• Anonymous
  September 06, 2005
  Yes, the catalog view is new to SQL Server 2005. You have to look in syscomments in older versions to get the defaults. Here is a sample query that shows defaults for all tables in a database:
  
  select OBJECT_NAME(c1.id) as table_name, c1.name as column_name, c2.text as default_text
  from syscolumns AS c1
  join syscomments AS c2
  on c1.cdefault = c2.id
  where c1.cdefault is not null
  order by table_name, column_name;
  
  --
  Umachandar

• Anonymous
  September 06, 2005
  <DIV>** Removed comment due to formatting issues while editing it. Reposted below. **</DIV>

• Anonymous
  September 07, 2005
  Thanks for the info (sql_modules). It was not clear what was provided for backward compatibility and what will be removed. Are you saying Create Default will be removed in future versions. This is a great site - keep it up.

• Anonymous
  September 07, 2005
  Defaults created by using create default is not exposed in sys.default_constraints since it is not declarative i.e., these are not specified at the time of creation of the table/column. Note that the CREATE DEFAULT feature is provided for backward compatibility reasons and it will be removed in a future version. To find the link between defaults created by CREATE DEFAULT and the column bound via sp_bindefault, you can use the query:
  
  select s.name as schema_name, t.name as table_name, c.name as column_name, object_name(c.default_object_id) as bound_to_default
  from sys.columns as c
  join sys.tables as t
  on t.object_id = c.object_id
  join sys.schemas as s
  on s.schema_id = t.schema_id
  where c.default_object_id is not null;
  
  To find the default text itself, you can use query below:
  
  select o.name as default_name, m.definition as default_text
  from sys.sql_modules as m
  join sys.objects as o
  on o.object_id = m.object_id
  where o.type = 'D';
  
  Note that the text is the entire CREATE DEFAULT statement. The catalog view sys.sql_modules is analogous to syscomments.
  
  --
  Umachandar

• Anonymous
  September 07, 2005
  I was referring to the CREATE DEFAULT statement. You can look at the CREATE DEFAULT topic in SQL Server 2005 Books Online for more details. The link for the topic is:
  
  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/08475db4-7d90-486a-814c-01a99d783d41.htm

• Anonymous
  September 16, 2005
  Determine primary keys and unique keys for all tables in a database using INFORMATION_SCHEMA views and the new catalog views in SQL Server 2005

• Anonymous
  March 29, 2006
  Hi,
    I was wondering if you have a script for SQL 2000. Ofcourse the one you have wouldnt work I believe bcause its for 2005. What I want is to find dependencies on a column in table in a database or different databases(broader sense).. dependencies like object, constraint etc..
  
  Thanks

• Anonymous
  October 31, 2006
  I was very interested in your post of Wednesday, September 07, 2005 where you described a way to retrieve the text of an sp. Is there a way to retrive the text of check constraints?  I'd like to generate ASP page code to perform validation on the client side.  I've been googling around and can't seem to find any way to do this.  Thanks

• Anonymous
  June 12, 2008
  Having been questioned about how to pull object / column dependencies through SQL I stumbled upon the

• Anonymous
  April 03, 2009
  PingBack from http://decipherinfosys.wordpress.com/2009/04/03/dropping-a-column-with-a-default-constraint/

• Anonymous
  June 15, 2009
  PingBack from http://edebtsettlementprogram.info/story.php?id=24288