System Schema & Documentation

[ckdinternet]

Anyone know how to query sysobjects & syproperties showing the table,
field name, and field properties (specifically the description)? I
have so far:

SELECT *
FROM sysproperties sp
INNER JOIN sysobjects so
ON sp.id = so.id

SELECT *
FROM syscolumns sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.type = 'U'

....not sure how to join the two. The 'ID' column refers to the table
ID and not the field ID.

[mountain man]

Re: System Schema & Documentation

"ckdinterne t" <ckdinternet@ya hoo.com> wrote in message
news:4ec97e90.0 402291941.1e3a1 070@posting.goo gle.com...[color=blue]
> Anyone know how to query sysobjects & syproperties showing the table,
> field name, and field properties (specifically the description)? I
> have so far:
>
> SELECT *
> FROM sysproperties sp
> INNER JOIN sysobjects so
> ON sp.id = so.id
>
> SELECT *
> FROM syscolumns sc
> INNER JOIN sysobjects so
> ON sc.id = so.id
> WHERE so.type = 'U'
>
> ...not sure how to join the two. The 'ID' column refers to the table
> ID and not the field ID.[/color]


Try this:

select substring(o.nam e,1,50) as "Table Name",
c.colid,
substring(c.nam e,1,30) as "Column Name",
substring(t.nam e,1,30) as "DataType",
c.length


from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t. xusertype)

--where substring(o.nam e,1,250) = @param

order by 1,2





Pete Brown
Falls Creek.
Oz

https://www.mountainman.com.au/software

[Simon Hayes]

Re: System Schema &amp; Documentation

ckdinternet@yah oo.com (ckdinternet) wrote in message news:<4ec97e90. 0402291941.1e3a 1070@posting.go ogle.com>...[color=blue]
> Anyone know how to query sysobjects & syproperties showing the table,
> field name, and field properties (specifically the description)? I
> have so far:
>
> SELECT *
> FROM sysproperties sp
> INNER JOIN sysobjects so
> ON sp.id = so.id
>
> SELECT *
> FROM syscolumns sc
> INNER JOIN sysobjects so
> ON sc.id = so.id
> WHERE so.type = 'U'
>
> ...not sure how to join the two. The 'ID' column refers to the table
> ID and not the field ID.[/color]

Here is one possible query:

select c.table_name, c.column_name, p.value
from information_sch ema.columns c
join sysproperties p
on object_id(c.tab le_name) = p.id
and c.ordinal_posit ion = p.smallid
order by c.table_name, c.column_name

Note that sysproperties is not documented, so in theory you shouldn't
reference it in code if possible, or at least not in production code.
But in this case, the only alternative is to use
fn_listextended properties(), which works fine for retrieving
individual properties, but is very difficult to use when you need
multiple properties in a set-based query.

Simon

[ckd internet]

Re: System Schema &amp; Documentation

Thanks for the replies.

I did this and it worked great! (off the record)

SELECT tab.name, col.name, prop.value
FROM sysobjects tab
INNER JOIN syscolumns col ON tab.id = col.id
LEFT OUTER JOIN sysproperties prop ON col.id = prop.id AND col.colid =
prop.smallid
WHERE tab.xtype = 'U'

C

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[lrsears]

Re: System Schema &amp; Documentation

I added a little based on another Groups post I found to list all the
column properties and descriptions of every table in my database using
this query:

--make a temporary table to hold list of table names
CREATE table #tablelist (table_name varchar(20))

INSERT INTO #tablelist --find all tables in the database SELECT
table_name FROM information_sch ema.tables WHERE table_type='BAS E TABLE'

--create a cursor loop over temp table to get all meta data

DECLARE @table_name varchar(20)
DECLARE table_Cursor CURSOR FOR
SELECT table_name from #tablelist
OPEN table_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM table_Cursor into @table_name
SELECT
table_name,
column_name,
data_type,
cast(des.value AS VARCHAR(4000)) AS col_desc
FROM information_sch ema.Columns col
LEFT OUTER JOIN

::fn_listextend edproperty(NULL ,'user','dbo',' table',@table_n ame,'column',de fault)
des
ON col.column_name =des.objname
WHERE table_name=@tab le_name
ORDER BY ORDINAL_POSITIO N

END
CLOSE table_Cursor
DEALLOCATE table_Cursor

DROP TABLE #tablelist
[color=blue]
>
>[/color]