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 (13)

  1. jorgen pedersen says:

    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

  2. brad chapman says:

    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.

  3. sqletips says:

    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

  4. sqletips says:

    <DIV>** Removed comment due to formatting issues while editing it. Reposted below. **</DIV>

  5. Brad Chapman says:

    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.

  6. sqletips says:

    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

  7. sqletips says:

    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

  8. 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

  9. Ajas Mohammed says:

    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

  10. Lou Gallo says:

    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

  11. David Benoit says:

    Having been questioned about how to pull object / column dependencies through SQL I stumbled upon the