Monday, March 19, 2012

Datamodel behind System Tables

L.S.,
How can I find out what the datamodel is behind the System Tables
(SysColumns, SysObjects, SysDatabases, etc.). I once came across a question
about finding out what the default value for a certain column in a certain
table was. There was talk about doing some heavy parsing of the result set
after using stored procedure sp_help(text). And even then it was not sure
that the desired result would be achieved, they said, leaving one to the
choice of looking up the actual SQL code.
However, after looking up some documentation on the System Tables and
guessing from there on where I might find the desired information, I found
out that one may find the default value for a certain column in a certain
table (or any other object for that matter) in the SysComments table. It
would have been a lot easier to find this out if I had had a datamodel of
those System Tables. Now, before using ER Studio, I was wondering if this
datamodel exists and if so, if it could be shared with the community at large.
Many thanks in advance,
Wilfred Damhuis
P.S.: replies may be send to wdyttg@.rubycon.demon.nl
Does this help you:
http://www.microsoft.com/sql/techinf.../systables.asp
"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> L.S.,
> How can I find out what the datamodel is behind the System Tables
> (SysColumns, SysObjects, SysDatabases, etc.). I once came across a
question
> about finding out what the default value for a certain column in a certain
> table was. There was talk about doing some heavy parsing of the result set
> after using stored procedure sp_help(text). And even then it was not sure
> that the desired result would be achieved, they said, leaving one to the
> choice of looking up the actual SQL code.
> However, after looking up some documentation on the System Tables and
> guessing from there on where I might find the desired information, I found
> out that one may find the default value for a certain column in a certain
> table (or any other object for that matter) in the SysComments table. It
> would have been a lot easier to find this out if I had had a datamodel of
> those System Tables. Now, before using ER Studio, I was wondering if this
> datamodel exists and if so, if it could be shared with the community at
large.
> Many thanks in advance,
> Wilfred Damhuis
> P.S.: replies may be send to wdyttg@.rubycon.demon.nl
|||Adam,
thanks, not only for your swift response, but also for the indeed very
helpfull info. But is it just me or is this little known?
Regards,
Wilfred Dmahuis
"Adam Machanic" wrote:

> Does this help you:
> http://www.microsoft.com/sql/techinf.../systables.asp
>
> "Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
> news:0B3B2E98-6C5E-45CD-BB2E-7E844B1D197F@.microsoft.com...
> question
> large.
>
>
|||"Rubycon" <Rubycon@.discussions.microsoft.com> wrote in message
news:EE0D57A7-EF61-4856-99B1-66EFEA8CFD0B@.microsoft.com...
> helpfull info. But is it just me or is this little known?
I have no idea how well known it is (according to the text in the link,
it's "very popular") but I only discovered it last week