list of user tables and their fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diane - 30 Aug 2005 23:38 GMT

Hello.

Thank you for taking the time to read my question.

Does anyone know how to pull a list of user tables and their associated
fields?

To give you a better idea, I imagine the statement to look something like
this -

Select userTableName, userTableField
From userTableInfo
GroupBy TableName, userTableField
OrderBy TableName, userTableField

The report will (hopefully) resemble something like this -

TableA
TableA.Field1
TableA.Field2
TableA.Field3
etc....
TableB
TableB.Field1
TableB.Field2
TableB.Field3
etc....
TableC
TableC.Field1
TableC.Field2
TableC.Field3
etc....
Etc.....

I'm not sure if table names and their fields are in one table, as I have not
worked with a DB without having documentation on the tables and fields.

Thank you for any tips or suggestions you can contribute!

Best-
Diane

Reply to this Message

Sue Hoegemeier - 31 Aug 2005 00:27 GMT

One option is something along the lines of:
select table_name, column_name
from information_schema.columns
where objectproperty(object_id(table_name),'IsTable') = 1
order by table_name, column_name

-Sue

>Hello.
>
[quoted text clipped - 37 lines]
>Best-
>Diane

Reply to this Message

Andrea Montanari - 31 Aug 2005 00:29 GMT

hi Diane,

> Hello.
>
[quoted text clipped - 10 lines]
> GroupBy TableName, userTableField
> OrderBy TableName, userTableField

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-i
z_87w3.asp

SET NOCOUNT ON
USE pubs
GO
SELECT c.TABLE_SCHEMA + '.' + c.TABLE_NAME AS [Table Name]
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
ORDER BY c.TABLE_SCHEMA + c.TABLE_NAME , c.ORDINAL_POSITION

> The report will (hopefully) resemble something like this -
>
> TableA
> TableA.Field1
> TableA.Field2
> TableA.Field3

this is a shaped result you can not directly have from SQL Server..

Signature

Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Reply to this Message

GregO - 31 Aug 2005 00:32 GMT

Hi Diane,

Try this from the database you want the details from

SELECT o.name AS s1, USER_NAME(o.uid) AS s2, C.name
FROM dbo.sysobjects o INNER JOIN
dbo.syscolumns C ON o.id = C.id
WHERE (OBJECTPROPERTY(o.id, N'IsTable') = 1) AND (OBJECTPROPERTY(o.id,
N'IsSystemTable') = 0) AND (o.name NOT LIKE N'#%')
ORDER BY o.name, C.colid

There more info in these two table so you should look in BOL for syscolumns
and sysobjects

Signature

kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com

> Hello.
>
[quoted text clipped - 38 lines]
> Best-
> Diane

Reply to this Message

Herbert - 31 Aug 2005 12:17 GMT

Hi,

Try this one

select A.Name as TableName,B.Name as ColumnName from
Sysobjects A,SysColumns B
where A.ID=B.ID and A.Type='U' order by A.Name

hope this will help

regards,
Herbert

> Hello.
>
[quoted text clipped - 37 lines]
> Best-
> Diane

Reply to this Message