Direct dependencies on a column...

re: Direct dependencies on a column...

jorgen pedersen — Tue, 06 Sep 2005 08:50:49 GMT

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

re: Direct dependencies on a column...

brad chapman — Wed, 07 Sep 2005 02:35:00 GMT

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.

re: Direct dependencies on a column...

SQL Server Engine Team — Wed, 07 Sep 2005 04:00:56 GMT

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

re: Direct dependencies on a column...

SQL Server Engine Team — Wed, 07 Sep 2005 04:24:32 GMT

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

re: Direct dependencies on a column...

Brad Chapman — Wed, 07 Sep 2005 18:46:08 GMT

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.

re: Direct dependencies on a column...

SQL Server Engine Team — Wed, 07 Sep 2005 21:33:14 GMT

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

re: Direct dependencies on a column...

SQL Server Engine Team — Wed, 07 Sep 2005 21:34:48 GMT

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

Determine primary keys and unique keys for all tables in a database...

SQL Server Engine Tips — Fri, 16 Sep 2005 21:32:58 GMT

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

For SQL Server 2000 - re: Direct dependencies on a column...

Ajas Mohammed — Wed, 29 Mar 2006 19:07:11 GMT

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

re: Direct dependencies on a column...

Lou Gallo — Wed, 01 Nov 2006 14:19:58 GMT

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

Table, Column, Replication Dependencies

David Benoit — Thu, 12 Jun 2008 17:49:36 GMT

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

Dropping a column with a default constraint « Systems Engineering and RDBMS

Fri, 03 Apr 2009 15:56:48 GMT

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

SQL Server Engine Tips Direct dependencies on a column | debt settlement program

SQL Server Engine Tips Direct dependencies on a column | debt settlement program — Mon, 15 Jun 2009 20:24:26 GMT

PingBack from http://edebtsettlementprogram.info/story.php?id=24288