sql server (alternate): 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

[quoted text, click to view]

Here is one possible query:

select c.table_name, c.column_name, p.value
from information_schema.columns c
join sysproperties p
on object_id(c.table_name) = p.id
and c.ordinal_position = 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_listextendedproperties(), which works fine for retrieving
individual properties, but is very difficult to use when you need
multiple properties in a set-based query.

[quoted text, click to view]


Try this:

select substring(o.name,1,50) as "Table Name",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,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.name,1,250) = @param

order by 1,2





Pete Brown
Falls Creek.
Oz
www.mountainman.com.au/software

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 ***

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_schema.tables WHERE table_type='BASE 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_schema.Columns col
LEFT OUTER JOIN

::fn_listextendedproperty(NULL,'user','dbo','table',@table_name,'column',default)
des
ON col.column_name=des.objname
WHERE table_name=@table_name
ORDER BY ORDINAL_POSITION

END
CLOSE table_Cursor
DEALLOCATE table_Cursor

DROP TABLE #tablelist

[quoted text, click to view]