How to GET The Name of the Default? -- sql server programming

sql server programming : How to GET The Name of the Default?

Dan Guzman
12/18/2004 8:57:04 AM

One method to list default constraints:

SELECT
USER_NAME(t.uid) AS TableSchema,
t.name AS TableName,
c.name AS ColumnName,
OBJECT_NAME(d.constid) AS ConstraintName
FROM sysobjects t
JOIN syscolumns c ON t.id = c.id
JOIN sysconstraints d ON d.id = t.id AND d.colid = c.colid
WHERE
t.xtype = 'U' AND
OBJECTPROPERTY(d.constid, 'IsDefaultCnst') = 1

You can explicitly name constraints with CREATE and ALTER table. Examples:

--during table create
CREATE TABLE EMP
(
EmpID int identity(100, 1),
ename varchar(25),
sal decimal(18,2)
CONSTRAINT DF_EMP_sal DEFAULT 0
)

--after table create
ALTER TABLE EMP
ADD CONSTRAINT DF_EMP_sal DEFAULT 0 FOR sal

--when adding new column
ALTER TABLE EMP
ADD sal decimal(18,2)
CONSTRAINT DF_EMP_sal DEFAULT 0

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

David Portas
12/18/2004 2:40:27 PM

It's better to give meaningful names to defaults, keys and constraints when
you create them:

CREATE TABLE emp
( ...
sal DECIMAL(18,2) CONSTRAINT df_emp_sal DEFAULT 0
)

If you script the table from Query Analyzer's Object Browser you will see
the name of the default. If you want to do it programmatically then see this
recent post:
http://www.google.co.uk/groups?selm=uSmddkX4EHA.3376%40TK2MSFTNGP12.phx.gbl

--
David Portas
SQL Server MVP
--

Andrea Montanari
12/18/2004 3:55:17 PM

hi Prabhat,
"Prabhat" <not_a_mail@hotmail.com> ha scritto nel messaggio
news:ex0hP0Q5EHA.2804@TK2MSFTNGP15.phx.gbl
[quoted text, click to view]

actually you can, specifying CONSTRAINT constraint_naim DEFAULT value, as
you can remove and re-add it using ALTER TABLE syntax

SET NOCOUNT ON
USE tempdb
CREATE TABLE test (
ID INT CONSTRAINT ID_DEFAULT DEFAULT 1
)
GO

/* ----- WARNING ----*/
/* use of sysobjects and syscolumns system tables */
/* in order to inspect for DEFAULT name based on */
/* table name and column name */

SELECT o1.name
FROM sysobjects o1 JOIN syscolumns c
ON o1.id = c.cdefault
JOIN sysobjects o2
ON o1.parent_obj = o2.id
WHERE (o2.name = 'test' ) AND (c.name = 'id')
GO
ALTER TABLE test
DROP CONSTRAINT ID_DEFAULT
GO
ALTER TABLE test
ADD CONSTRAINT DEF_new_def_for_col_ID DEFAULT 10 FOR ID
GO
SELECT o1.name
FROM sysobjects o1 JOIN syscolumns c
ON o1.id = c.cdefault
JOIN sysobjects o2
ON o1.parent_obj = o2.id
WHERE (o2.name = 'test' ) AND (c.name = 'id')

GO
DROP TABLE test
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Eric Sabine
12/18/2004 4:37:02 PM

After reading the other posts I was going to suggest the INFORMATION_SCHEMA
views but to my surprise could not find the name of a default constraint in
any of them. Perhaps default constraints are not compliant with ANSI
standards as the aforementioned views are. But I did find these new views
from Dejan Sarka; INFORMATION_SCHEMA.DEFAULT_CONSTRAINTS and
INFORMATION_SCHEMA.DEFAULT_CONSTRAINT_COLUMN_USAGE

Thanks to Dejan for this nice code.

This site, http://sql.reproms.si/live/default.asp contains the code. Go to
the code library, T-SQL, and the views are on the second page.

hth
Eric

[quoted text, click to view]

Prabhat
12/18/2004 7:50:01 PM

Hi All,

Is there any way we could find out the NAME of the default that is binded to
a column on a table?

Ex: -

Create Table EMP
(
EmpID int identity(100, 1),
ename varchar(25),
sal decimal(18,2) default 0
)

So Here How Do I find the Name of the Default that is assigned to SAL
Column. (I know that I can see that using SP_HELP, But How do I store that
Value in a Variable from a SYSTEM TABLE?)

And IS there a Way to Give the Default Name in column definations? (Not
using the sp_binddefault)

Thanks
Prabhat

David Portas
12/19/2004 12:03:18 PM

[quoted text, click to view]

AFAIK the ANSI/ISO Standard (upto SQL99 anyway) does not support named
defaults, which would explain why there is no place for them in the
INFORMATION_SCHEMA. This is logical enough. Only one default is permitted
per column so it is sufficient to identify them in DDL statements just using
the column name. The default *value* is exposed through the COLUMNS view.

The default CONSTRAINT syntax is a proprietary innovation, and always
strikes me as a bit illogical. DEFAULTs are not *constraints* in the same
sense as the other constructs of that name.

--
David Portas
SQL Server MVP
--

Prabhat
12/20/2004 1:05:03 PM

Thanks All for your suggestion. The best way to define the defaults are
Clear to me now.

Thanks
Prabhat

[quoted text, click to view]

Prabhat
12/20/2004 1:05:29 PM

Thanks David!

[quoted text, click to view]

Prabhat
12/20/2004 3:00:11 PM

Hi Andrea!

Thanks for the easy solution.
Prabhat

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn