How Can I Get a List of the Fields in a Table?
-
i need to generate a list of the field names in a table, with or without properties (that's not important), so that i can copy, paste, and sort the list.
as far as i can tell, this is not doable via the design mode of the table. there must be a command that will return such a list. right?
thanks
deek
-
select cast(so.name as char(30)) as TableName, sc.colorder, sc.name as ColumnName
from sysobjects so, syscolumns sc
where so.id = sc.id
and so.type = 'u'
and so.name = 'batchheader'
order by so.name, sc.number
-
i put the table name in place of TableName in your code; but I'm getting an empty set when I run the code. how else do i need to modify the code?
-
Deek, The above worked for me, but if your "TableName" is actually a VIEW or not a "Table", the so.type = 'u' is limiting your results. Try and so.type = 'v' or and so.type like '[uv]'.
If you are in Query Analy., and just want a comma deli column list, open the object browser, navigate to the table you are interesed in, Expand it's [tree], then drag it's "Columns" into your code, not each column, the word "Columns" itself.
Once you understand the BITs, all the pieces come together
-
Sorry, you need to expand the "Columns" prior to dragging it.
Once you understand the BITs, all the pieces come together -
Okay, I did get the desired result using the Object Browser/ drag 'Columns' method. Thank you.
Incidentally, though, I'd like to know why the code did not generate the desired result. The TableName was indeed a table in the same database I was working in, a database to which I am the owner. I tried the '[uv]', and also I tried it with other tables; and each time I get an empty set.
For example, when I tried to run this on a table called 'SnapshotNbsMsMsALL', the result was an empty set of 3 columns, which read:
SnapshotNbsMsMsALL colorder ColumnName
any tips on how to rectify?
thanks
-
The ANSI schema views work pretty well for this sort of thing...
SELECT Column_Name
FROM Information_Schema.Columns
WHERE Table_Name = 'SnapshotNbsMsMsALL'
ORDER BY Ordinal_Position
--Jonathan -
In Query Analyzer, type:
-
I go into Ent Mgr, highlight all of the rows of column info, then paste into Excel. Seems to work well if you're just using this info on-the-fly. You even get a column that shows the primary key info. Of course if you have to do this programmatically or have some end-use in mind, this may not be the best solution.
-
I would stay away from querying the system tables, when there is another solution at hand. Use Jonathan's suggestion, that's what the INFORMATION_SCHEMA stuff is for.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
And in QA, you can always use sp_help <tablename>.
Mark -
Here's a quick little script I use. I was trying to emulate the Oracle DESC <table> command.
SELECT case ORDINAL_POSITION
when 1 then left(TABLE_NAME, 25)
else ' '
end 'Table Name',
left(COLUMN_NAME, 25) 'Column Name',
left(DATA_TYPE, 15) 'Data Type',
CHARACTER_MAXIMUM_LENGTH 'Length',
case IS_NULLABLE
when 'Yes' then 'NULL'
else 'NOT NULL'
end As 'Null',
COLUMN_DEFAULT 'Default Value'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME in (select o.name
from sysobjects o
where o.type ='U')
order by TABLE_NAME, ORDINAL_POSITION
"Keep Your Stick On the Ice" ..Red Green -
In Query Analyser type Exec SP_Help tablename . You may have problems if your owner is not DBO, as SP_Help does not let you prefix the table name with an owner. If this is the case you need to log on as the owner I believe to overcome this.
-
The simplest solution to getting a simple list of fields is to run the following sql in query analyzer with the output set to comma delimited text.
select * from tablename where 1 = -1
You can now copy the field names from the results and paste in excel and split into columns and sort as you desire.
Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com -
Here is a script I use to get a little more detailed information about a given table:
declare @TableName varchar(40)
set @TableName = 'TABLENAME'
select distinct
ServerName = @@ServerName,
DBName = db_name(),
TableName =so.name,
ColumnName =sc.name,
st.name as DataType,
DataLength = cast(sc.length as varchar(4))+' bytes',
sc.colid
from sysobjects so
join syscolumns sc
on so.id = sc.id
join systypes st
on sc.xtype = st.xtype
where so.type = 'u'
and so.name = @TableName
order by so.name,sc.colid
Actually, I use this in a cursor to get complete table structures for a given database. I run the cursor against all databases on a server, a simple select on master..sysdatabases, and save the info in a utility table. I do this for all of my servers, 37 currently and growing, to keep up with any modifications. I do much the same with indexes. I have found many duplicate indexes doing so.
Good Luck
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply