listing columns name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

listing columns name

Author

Topic

rohans
Posting Yak Master

194 Posts

Posted - 2004-09-15 : 16:05:14

How may I get a list of all the column names in a table in my database?

All help appreciated.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-15 : 16:14:14

Query the system tables.


SELECT	dbo.sysobjects.name AS ObjectName,
	dbo.sysobjects.id AS ObjectID,
	dbo.syscolumns.name AS ColumnName, 
	dbo.systypes.name AS DataType, 
	dbo.syscolumns.length,
FROM    dbo.sysobjects 	INNER JOIN dbo.syscolumns ON (dbo.sysobjects.id = dbo.syscolumns.id)
	INNER JOIN dbo.systypes ON (dbo.syscolumns.xusertype = dbo.systypes.xusertype)
WHERE	dbo.sysobjects.name = @NameOfTableOrView

rohans
Posting Yak Master

194 Posts

Posted - 2004-09-15 : 16:32:48

Thankx but I got something that worked and it seem a bit shorter

select col.name,obj.name, obj.type, obj.xtype from sysobjects obj
inner join syscolumns col
on col.id = obj.id
where obj.[name] like '%tablename%'

All help appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-15 : 16:34:52

Do not use the system objects for this. Use the INFORMATION_SCHEMA views. There is a view called COLUMNS that will give you what you need.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

Modify the query as needed.

Tara

rohans
Posting Yak Master

194 Posts

Posted - 2004-09-15 : 16:45:51

Tara,
Great, I never knew about the information schema, it will now save me nuff heart ache where getting info on the DB is concerned.

All help appreciated.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-09-15 : 17:30:44

Also have a look at http://www.sqlteam.com/item.asp?ItemID=13122.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources