sql server programming: field definitions for all user tables -- sql server programming

field definitions for all user tables

Diane
10/6/2005 7:20:02 PM

sql server programming:

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

Re: field definitions for all user tables

R.D
10/6/2005 10:46:02 PM

my choice is
sp_msforeachtable @command1 = "sp_help '?' "
--
Regards
R.D
--Knowledge gets doubled when shared

[quoted text, click to view]

Re: field definitions for all user tables

Mike Hodgson
10/7/2005 12:00:00 AM

I 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

[quoted text, click to view]

Re: field definitions for all user tables

Aaron Bertrand [SQL Server MVP]
10/7/2005 7:20:27 AM

[quoted text, click to view]

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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.