Getting a column list using QA?

[NSMan]

Is there a way to select the property of a table, and have Query Anylyzer return a list of columns that exist in the table?

 

[mrdenny]

Yep, use the INFORMATION_SCHEMA views.

select *
from INFORMATION_SCHEMA.columns
where TABLE_NAME = 'Table'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[NSMan]

Talk about quick service! Thanks!

 

[nomadicalloy]

you can use following

SELECT a.[name] as 'Table',
b.[name] as 'Column',
c.[name] as 'Datatype',
b.[length] as 'Length',
CASE
WHEN b.[cdefault] > 0 THEN d.[text]
ELSE NULL
END as 'Default',
CASE
WHEN b.[isnullable] = 0 THEN 'No'
ELSE 'Yes'
END as 'Nullable'
FROM sysobjects a
INNER JOIN syscolumns b
ON a.[id] = b.[id]
INNER JOIN systypes c
ON b.[xtype] = c.[xtype]
LEFT JOIN syscomments d
ON b.[cdefault] = d.[id]
WHERE a.[xtype] = 'u'
-- 'u' for user tables, 'v' for views.
and a.[name]='table name'
AND a.[name] <> 'dtproperties'
ORDER BY a.[name],b.[colorder]



SQL Help Blog

http://www.xpherion.com/blog.asp

 

[mrdenny]

That seams to pull the same info as the INFORMATION_SCHEMA view. So why do all the typing?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pwilson]

Is the information scheme different from

exec sp_columns "Table"

 

[vongrunt]

Yes. Somewhat different columns in return set. Information schema views are by SQL-92 standard, while sp_columns is not.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[philhege]

Best to stick with the schema views. If you've seen the system views diagram for SQL2005, you'd understand why they recommend you don't drill into the tables.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[mrdenny]

Microsoft has also promised to not change the INFORMATION_SCHEMA views (at least they won't remove any columns). The system tables can and do (sometimes) change with each build (service pack, hot fix, etc).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com