How do I get all column descriptions from a schema.table?

  • Thread starter Thread starter David Thielen
  • Start date Start date
D

David Thielen

Hi;

I need to get all column descriptions from a table where a database
may have two tables with the same name but different schemas. How can
I do this? (And is there a simplier select to do this?)

Note see below for why GetSchema will not work.


To get all columns descriptions I use:

Version 8:
cmd.CommandText = "SELECT COLUMN_NAME,
sysproperties.[value] FROM INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
" +

"sysproperties ON sysproperties.id =
OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA + '.' + " +

"INFORMATION_SCHEMA.COLUMNS.TABLE_NAME) AND sysproperties.smallid =
INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION " +
"AND
sysproperties.name = 'MS_Description' WHERE
(OBJECTPROPERTY(OBJECT_ID(INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA " +
"+
'.' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME), 'IsMsShipped') = 0) AND
(TABLE_NAME = @p1)";
cmd.Parameters.Add(MakeParam("@p1",
table));

Version 9:
cmd.CommandText = "SELECT c.name AS
[Column Name], ex.value AS Description FROM sys.columns AS c INNER
JOIN " +
"sys.extended_properties AS ex
ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name
= " +
"'MS_Description' AND
ex.class_desc = 'OBJECT_OR_COLUMN' " +
"WHERE
(OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0) AND
(OBJECT_NAME(c.object_id) = @p1)";
cmd.Parameters.Add(MakeParam("@p1",
table));


I cannot use GetSchema() because:

1) Does not provide the description for a column.
2) Columns in a table are not returned in metadata order (which users
expect).

??? - thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi David,

As I have said in another your case: [Is it impossible to have schema1.dave
and schema2.dave as tables in the same DB?], data tables with same name and
different schemas are supported in SQL Server, because schema name is also
one of the identifiers for the objects in SQL Server.

To retrieve the column descriptions, you can consider using the following
simpler T-SQL commands:
=======================================================================
select sys.columns.name as ColumnName,
(select value from sys.extended_properties where
sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as
ColumnDescription
from sys.columns, sys.tables, sys.types,sys.schemas where
sys.columns.object_id = sys.tables.object_id and
sys.columns.user_type_id=sys.types.user_type_id and
sys.tables.schema_id=sys.schemas.schema_id and
sys.tables.name=@table_name and sys.schemas.name = @schema_name
=======================================================================

We can filter the data by the table name and schema name in this SQL
commands.


Another option is to use SQL Server Management Objects (SMO). With SMO, we
can almost retrieve all the SQL Server objects to the local application.
However, the disadvantage is its expensive performance cost. For
additional information about SMO, please see
http://msdn.microsoft.com/en-us/library/ms162169.aspx
http://www.codeproject.com/KB/database/SMODemo.aspx


If you have any questions, please feel free to let me know.

Have a nice day, David!


Best Regards,
Lingzhi Sun
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hi David,

As I have said in another your case: [Is it impossible to have schema1.dave
and schema2.dave as tables in the same DB?], data tables with same name and
different schemas are supported in SQL Server, because schema name is also
one of the identifiers for the objects in SQL Server.

To retrieve the column descriptions, you can consider using the following
simpler T-SQL commands:
=======================================================================
select sys.columns.name as ColumnName,
(select value from sys.extended_properties where
sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as
ColumnDescription
from sys.columns, sys.tables, sys.types,sys.schemas where
sys.columns.object_id = sys.tables.object_id and
sys.columns.user_type_id=sys.types.user_type_id and
sys.tables.schema_id=sys.schemas.schema_id and
sys.tables.name=@table_name and sys.schemas.name = @schema_name
=======================================================================
...
Click to expand...

Hi;

Two problems with the above. The big problem is Sql Server 2000 does
not use the above - does not have sys.extended_properties.

Second problem is I also need the data type for each column which I
assume is an additional table that has to be included in the select. I
tried to find what table but couldn't.

I can use different selects based on the version of Sql Server. But
for versions of Sql Server 2000/2005/2008 how can I, from a connection
to a specific database, setting the table & schema in the select, get
back all columns, in metadata order, where it gives me:

1) Column name
2) Column data type (which I map to SqlDbType)
3) Column description

And if in this select, for a column that is a foreign key, if it could
also return the schema, table, & column of the primary key it maps to
- that would be fantastic.

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi David,

In SQL Server 2000, you can consider using the following SQL commands to
retrieve the detailed column information in the data table:
================================================
select user_name(a.uid) as username
,b.name as colname
,h.id as primarykey
,type_name(b.xusertype) as type
,b.length
,b.isnullable as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as description
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where
f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and
(f.status&2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and object_name(a.id) = 'TableName' and user_name(a.uid)= 'SchemaName'
order by b.colid
================================================

In SQL 20005/2008, to retrieve the column type as well, please refer to the
following SQL commands:
================================================
select sys.columns.name as ColumnName,
sys.types.name as ColumnType,
(select value from sys.extended_properties where
sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as
ColumnDescription
from sys.columns, sys.tables, sys.types,sys.schemas where
sys.columns.object_id = sys.tables.object_id and
sys.columns.user_type_id=sys.types.user_type_id and
sys.tables.schema_id=sys.schemas.schema_id and
sys.tables.name= 'TableName' and sys.schemas.name = 'SchemaName'
================================================


Besides, for the PK/FK question, let¡¯s discuss it in detail in your
another newsgroup case: [How do I get all FK:PK mappings with the schema
info?].

If you have any questions or concerns, please feel free to let me know.

Have a nice day!


Best Regards,
Lingzhi Sun
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Hi David,

In SQL Server 2000, you can consider using the following SQL commands to
retrieve the detailed column information in the data table:
================================================
select user_name(a.uid) as username
,b.name as colname
,h.id as primarykey
,type_name(b.xusertype) as type
,b.length
,b.isnullable as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as description
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where
f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and
(f.status&2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and object_name(a.id) = 'TableName' and user_name(a.uid)= 'SchemaName'
order by b.colid
================================================

In SQL 20005/2008, to retrieve the column type as well, please refer to the
following SQL commands:
================================================
select sys.columns.name as ColumnName,
sys.types.name as ColumnType,
(select value from sys.extended_properties where
sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as
ColumnDescription
from sys.columns, sys.tables, sys.types,sys.schemas where
sys.columns.object_id = sys.tables.object_id and
sys.columns.user_type_id=sys.types.user_type_id and
sys.tables.schema_id=sys.schemas.schema_id and
sys.tables.name= 'TableName' and sys.schemas.name = 'SchemaName'
================================================
...
Click to expand...

Worked great - thanks - dave (amazing selects)

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi David,

In SQL Server 2000, you can consider using the following SQL commands to
retrieve the detailed column information in the data table:
================================================
select user_name(a.uid) as username
,b.name as colname
,h.id as primarykey
,type_name(b.xusertype) as type
,b.length
,b.isnullable as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as description
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where
f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and
(f.status&2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and object_name(a.id) = 'TableName' and user_name(a.uid)= 'SchemaName'
order by b.colid
================================================

In SQL 20005/2008, to retrieve the column type as well, please refer to the
following SQL commands:
================================================
select sys.columns.name as ColumnName,
sys.types.name as ColumnType,
(select value from sys.extended_properties where
sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as
ColumnDescription
from sys.columns, sys.tables, sys.types,sys.schemas where
sys.columns.object_id = sys.tables.object_id and
sys.columns.user_type_id=sys.types.user_type_id and
sys.tables.schema_id=sys.schemas.schema_id and
sys.tables.name= 'TableName' and sys.schemas.name = 'SchemaName'
================================================
...
Click to expand...

I tried the second on Adventureworks and this select (Server
Management Studio changed the wording when run) gets an error -
subquery returned more than 1 value.

SELECT sys.columns.name AS ColumnName, sys.types.name AS
ColumnType,
(SELECT value
FROM sys.extended_properties
WHERE (major_id =
sys.columns.object_id) AND (minor_id = sys.columns.column_id)) AS
ColumnDescription
FROM sys.columns INNER JOIN
sys.tables ON sys.columns.object_id =
sys.tables.object_id INNER JOIN
sys.types ON sys.columns.user_type_id =
sys.types.user_type_id INNER JOIN
sys.schemas ON sys.tables.schema_id =
sys.schemas.schema_id
WHERE (sys.tables.name = 'Employee') AND (sys.schemas.name =
'HumanResources')

Any ideas?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm
 
Hi David,

The error is thrown because except column description, the
HumanResources.Employee data table in Adventureworks database has index
description as well. So the subquery will return more than one value.

To avoid this issue and only retrieve the column description, we can filter
the data by (class) = 1, because the column class is equal to 1 in the
sys.entended_properties
(http://msdn.microsoft.com/en-us/library/ms177541.aspx).
======================================================
SELECT sys.columns.name AS ColumnName,
sys.types.name AS ColumnType,
(SELECT value FROM sys.extended_properties
WHERE (major_id = sys.columns.object_id) AND
(minor_id = sys.columns.column_id) AND
((class) = 1)) AS ColumnDescription
FROM sys.columns
INNER JOIN sys.tables ON sys.columns.object_id = sys.tables.object_id
INNER JOIN sys.types ON sys.columns.user_type_id = sys.types.user_type_id
INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
WHERE (sys.tables.name = 'Employee') AND (sys.schemas.name =
'HumanResources')
======================================================

Besides, to retrieve the other descriptions, we can filter the by setting
the (class) value, e.g. Database = 0, Schema = 3, index = 7 and etc.

If you have any questions, please feel free to let me know.

Have a nice day!

Best Regards,
Lingzhi Sun
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Back
Top