list of user tables and their fields -- sql server new users

Sue Hoegemeier
8/30/2005 5:27:06 PM

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

On Tue, 30 Aug 2005 15:38:01 -0700, "Diane"
[quoted text, click to view]

GregO
8/31/2005 12:00:00 AM

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

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

[quoted text, click to view]

Andrea Montanari
8/31/2005 1:29:54 AM

hi Diane,
[quoted text, click to view]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_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

[quoted text, click to view]

this is a shaped result you can not directly have from SQL Server..
--
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

Herbert
8/31/2005 4:17:03 AM

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

[quoted text, click to view]

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 about sql server new users

sql server new users : list of user tables and their fields