field definitions for all user tables

Hello.
Thank you in advance for reading this question.
I posted this question in the newbie area, but have not had a response.
Is there a way to get all the fields for all user tables that are returned
when you do sp_help tablename? I think I have found the columns I need in
'syscolumns' but it is not clear to me how to link the table names to that
syscolumns table to get what I need.
My apolgies for the newbie question. Any help is appreciated!
DianeI just replied to the newusers post.
However, to get table names from syscolumns you can simply call the
OBJECT_NAME() function passing in the id column from syscolumns
(although it's more kosher to use the INFORMATION_SCHEMA views). Like this:
select object_name(id) as [TableName], * from dbo.syscolumns
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Diane wrote:

>Hello.
>Thank you in advance for reading this question.
>I posted this question in the newbie area, but have not had a response.
>Is there a way to get all the fields for all user tables that are returned
>when you do sp_help tablename? I think I have found the columns I need in
>'syscolumns' but it is not clear to me how to link the table names to that
>syscolumns table to get what I need.
>My apolgies for the newbie question. Any help is appreciated!
>Diane
>
>|||> Is there a way to get all the fields for all user tables that are returned
> when you do sp_help tablename? I think I have found the columns I need in
> 'syscolumns'
I strongly recommend avoiding the sys tables when possible.
How about http://www.aspfaq.com/2177 ?|||my choice is
sp_msforeachtable @.command1 = "sp_help '?' "
--
Regards
R.D
--Knowledge gets doubled when shared
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly recommend avoiding the sys tables when possible.
> How about http://www.aspfaq.com/2177 ?
>
>|||> my choice is
> sp_msforeachtable @.command1 = "sp_help '?' "
Well, sp_msforeachtable is convenient, sure. However I have a two issues
with this approach:
(a) it returns a resultset for each table, whereas with
INFORMATION_SCHEMA.COLUMNS you can easily get all the results in a single
set.
(b) it is an undocumented and unsupported feature, so its behavior can
change or it could disappear from the product altogether with a service
pack, security release or new version of SQL Server.