I am trying to get the names of the tables and the column names from the
code below for a database but it is not working. When I run the query
below the column titles are delivered but there is no data. I think this
might be a premissions issue. Has anyone run into this before?
Thanks in advance.
Use Test_db
/* Provides Table Name, Column Name, Extened Description */
Select a.name as tbl_name, b.name as column_name, d.name as data_type,
d.length as length, d.xprec as prec, d.scale as scale, b.usertype,
b.scale, c.value
from sysobjects as a inner join syscolumns as b on a.id=b.id inner join
sysproperties as c on b.colid=c.smallid and a.id=c.id
inner join systypes as d on b.xtype=d.xtypeJeff,
This query will only display rows for columns that have extended properties assigned to them with sp_addextendedproperty. If you need to
see all columns, you can use outer joins instead of inner joins.
For the record, the [sysproperties] table is undocumented and unsupported. Here's an alternative, though a messy one:
http://groups.google.com/groups?q=4...03-E3F3F8312314
Steve Kass
Drew University
Jeff Magouirk wrote:
> Dear list,
> I am trying to get the names of the tables and the column names from the
> code below for a database but it is not working. When I run the query
> below the column titles are delivered but there is no data. I think this
> might be a premissions issue. Has anyone run into this before?
> Thanks in advance.
>
>
> Use Test_db
> /* Provides Table Name, Column Name, Extened Description */
> Select a.name as tbl_name, b.name as column_name, d.name as data_type,
> d.length as length, d.xprec as prec, d.scale as scale, b.usertype,
> b.scale, c.value
> from sysobjects as a inner join syscolumns as b on a.id=b.id inner join
> sysproperties as c on b.colid=c.smallid and a.id=c.id
> inner join systypes as d on b.xtype=d.xtype
>|||Thank you for the answer, but I am new to the SQL Server world and I am
not sure how to apply the
extended properties with the sp_addextendedproperty function.
I have tried after reading the online books.
Thanks in advance.
Jeff
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
