get default value

Hi, i'm working on an asp project that will work similar to enterprise
manager for remotely managing some aspects of a sql database. What i'm
stuck on is how (in vbscript or with SQL commands) can i find out what the
default value (if any) is for a given column of a given table? I know there
are some stored procedures and sql command i'm using to find whether columns
are primary keys, identities, nullable, what there size is, etc, but i can't
figure out how to find a columns default value.

thanks in advance

Aaron
Wed Dec 03 12:11:09 CST 2003

Here's an example:


CREATE TABLE splunge
(
splungeID INT IDENTITY(1,1),
dt DATETIME DEFAULT GETDATE(),
i INT DEFAULT 1,
c CHAR(3) DEFAULT 'foo',
blat VARCHAR(32)
)
GO

SELECT
scol.name,
scom.text
FROM
sysconstraints scon
INNER JOIN syscolumns scol
ON scon.id = scol.id AND scon.colid = scol.colid
INNER JOIN syscomments scom
ON scon.constid = scom.id
WHERE
OBJECT_NAME(scon.id) = 'splunge'

GO

DROP TABLE splunge
GO


If you want a specific column, use WHERE scol.name = 'column_name'

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"Chris Coho, Jr." <ccoho@jrwald.com> wrote in message
news:OXP4FdcuDHA.2060@TK2MSFTNGP10.phx.gbl...
> Hi, i'm working on an asp project that will work similar to enterprise
> manager for remotely managing some aspects of a sql database. What i'm
> stuck on is how (in vbscript or with SQL commands) can i find out what the
> default value (if any) is for a given column of a given table? I know
there
> are some stored procedures and sql command i'm using to find whether
columns
> are primary keys, identities, nullable, what there size is, etc, but i
can't
> figure out how to find a columns default value.
>
> thanks in advance
>
>

Ray
Wed Dec 03 12:14:36 CST 2003

You can do


select column_name,column_default
from information_schema.columns
where table_name = 'tablename'

HTH

--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Coho, Jr." <ccoho@jrwald.com> wrote in message
news:OXP4FdcuDHA.2060@TK2MSFTNGP10.phx.gbl...
> Hi, i'm working on an asp project that will work similar to enterprise
> manager for remotely managing some aspects of a sql database. What i'm
> stuck on is how (in vbscript or with SQL commands) can i find out what the
> default value (if any) is for a given column of a given table? I know
there
> are some stored procedures and sql command i'm using to find whether
columns
> are primary keys, identities, nullable, what there size is, etc, but i
can't
> figure out how to find a columns default value.
>
> thanks in advance
>
>

Aaron
Wed Dec 03 12:17:15 CST 2003

Mmm, much cleaner than mine.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/




"Ray Higdon" <rayhigdon@higdonconsulting.com> wrote in message
news:edBgSlcuDHA.2464@TK2MSFTNGP12.phx.gbl...
> You can do
>
>
> select column_name,column_default
> from information_schema.columns
> where table_name = 'tablename'

Chris
Wed Dec 03 12:30:11 CST 2003

excellent, thanks guys, that helps a ton

I may look through thos information_schema tables to see what other goodies
they can show me!

"Ray Higdon" <rayhigdon@higdonconsulting.com> wrote in message
news:edBgSlcuDHA.2464@TK2MSFTNGP12.phx.gbl...
> You can do
>
>
> select column_name,column_default
> from information_schema.columns
> where table_name = 'tablename'
>
> HTH
>
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Chris Coho, Jr." <ccoho@jrwald.com> wrote in message
> news:OXP4FdcuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > Hi, i'm working on an asp project that will work similar to enterprise
> > manager for remotely managing some aspects of a sql database. What i'm
> > stuck on is how (in vbscript or with SQL commands) can i find out what
the
> > default value (if any) is for a given column of a given table? I know
> there
> > are some stored procedures and sql command i'm using to find whether
> columns
> > are primary keys, identities, nullable, what there size is, etc, but i
> can't
> > figure out how to find a columns default value.
> >
> > thanks in advance
> >
> >
>
>

Ray
Wed Dec 03 12:33:42 CST 2003

Lots of goodies:

TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
COLUMN_DEFAULT
IS_NULLABLE
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
COLLATION_NAME


--
Ray Higdon MCSE, MCDBA, CCNA
--
"Chris Coho, Jr." <ccoho@jrwald.com> wrote in message
news:eFq02rcuDHA.2072@TK2MSFTNGP10.phx.gbl...
> excellent, thanks guys, that helps a ton
>
> I may look through thos information_schema tables to see what other
goodies
> they can show me!
>
> "Ray Higdon" <rayhigdon@higdonconsulting.com> wrote in message
> news:edBgSlcuDHA.2464@TK2MSFTNGP12.phx.gbl...
> > You can do
> >
> >
> > select column_name,column_default
> > from information_schema.columns
> > where table_name = 'tablename'
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Chris Coho, Jr." <ccoho@jrwald.com> wrote in message
> > news:OXP4FdcuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > Hi, i'm working on an asp project that will work similar to enterprise
> > > manager for remotely managing some aspects of a sql database. What
i'm
> > > stuck on is how (in vbscript or with SQL commands) can i find out what
> the
> > > default value (if any) is for a given column of a given table? I know
> > there
> > > are some stored procedures and sql command i'm using to find whether
> > columns
> > > are primary keys, identities, nullable, what there size is, etc, but i
> > can't
> > > figure out how to find a columns default value.
> > >
> > > thanks in advance
> > >
> > >
> >
> >
>
>

Tibor
Thu Dec 04 03:18:19 CST 2003

IF you want to look at a diagram of the views, check out:
http://www.dbmaint.com/info_schema.asp

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver


"Chris Coho, Jr." <ccoho@jrwald.com> wrote in message news:eFq02rcuDHA.2072@TK2MSFTNGP10.phx.gbl...
> excellent, thanks guys, that helps a ton
>
> I may look through thos information_schema tables to see what other goodies
> they can show me!
>
> "Ray Higdon" <rayhigdon@higdonconsulting.com> wrote in message
> news:edBgSlcuDHA.2464@TK2MSFTNGP12.phx.gbl...
> > You can do
> >
> >
> > select column_name,column_default
> > from information_schema.columns
> > where table_name = 'tablename'
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Chris Coho, Jr." <ccoho@jrwald.com> wrote in message
> > news:OXP4FdcuDHA.2060@TK2MSFTNGP10.phx.gbl...
> > > Hi, i'm working on an asp project that will work similar to enterprise
> > > manager for remotely managing some aspects of a sql database. What i'm
> > > stuck on is how (in vbscript or with SQL commands) can i find out what
> the
> > > default value (if any) is for a given column of a given table? I know
> > there
> > > are some stored procedures and sql command i'm using to find whether
> > columns
> > > are primary keys, identities, nullable, what there size is, etc, but i
> > can't
> > > figure out how to find a columns default value.
> > >
> > > thanks in advance
> > >
> > >
> >
> >
>
>