SQL 你如何返回表的列名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/600446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you return the column names of a table?
提问by Belliez
How would I return the column names of a table using SQL Server 2008? i.e. a table contains these columns- id, name, address, country and I want to return these as data.
如何使用 SQL Server 2008 返回表的列名？即一个表包含这些列 - id、姓名、地址、国家，我想将这些作为数据返回。
回答by Gulzar Nazim
Not sure if there is an easier way in 2008 version.
不确定 2008 版本中是否有更简单的方法。
USE [Database Name]
SELECT COLUMN_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName'
回答by DiggDev
This is the easiest way
这是最简单的方法
exec sp_columns [tablename]
回答by Paul Lefebvre
Something like this?
像这样的东西？
sp_columns @table_name=your table name
回答by splattne
One method is to query syscolumns:
一种方法是查询 syscolumns：
select
syscolumns.name as [Column],
syscolumns.xusertype as [Type],
sysobjects.xtype as [Objtype]
from
sysobjects
inner join
syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and sysobjects.name = 'MyTableName'
order by syscolumns.name
回答by HogHunter
This seems a bit easier then the above suggestions because it uses the OBJECT_ID()function to locate the table's id. Any column with that id is part of the table.
这似乎比上面的建议容易一些，因为它使用OBJECT_ID()函数来定位表的 id。具有该 ID 的任何列都是表的一部分。
SELECT *
FROM syscolumns
WHERE id=OBJECT_ID('YOUR_TABLE')
I commonly use a similar query to see if a column I know is part of a newer version is present. It is the same query with the addition of {AND name='YOUR_COLUMN'} to the where clause.
我通常使用类似的查询来查看我知道的列是否存在于较新版本中。这是在 where 子句中添加 {AND name='YOUR_COLUMN'} 的相同查询。
IF EXISTS (
SELECT *
FROM syscolumns
WHERE id=OBJECT_ID('YOUR_TABLE')
AND name='YOUR_COLUMN'
)
BEGIN
PRINT 'Column found'
END
回答by Shanmugam Sudalaimuthu
try this
尝试这个
select * from <tablename> where 1=2
...............................................
......................................................
回答by Edi Mohseni
The following seems to be like the first suggested query above but sometime you have to specify the database to get it to work. Note that the query should also work without specifying the TABLE_SCHEMA:
以下似乎类似于上面的第一个建议查询，但有时您必须指定数据库才能使其工作。请注意，查询也应该在不指定 TABLE_SCHEMA 的情况下工作：
SELECT COLUMN_NAME
FROM YOUR_DB_NAME.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME' AND TABLE_SCHEMA = 'YOUR_DB_NAME'
回答by Scott K.
I use
我用
SELECT st.NAME, sc.NAME, sc.system_type_id
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
WHERE st.name LIKE '%Tablename%'
回答by angieb1008
Why not just try this:
为什么不试试这个：
right click on the table -> Script Table As -> Create To -> New Query Editor Window?
右键单击表 -> Script Table As -> Create To -> New Query Editor Window？
The entire list of columns are given in the script. Copy it and use the fields as necessary.
脚本中给出了整个列列表。复制它并根据需要使用这些字段。
回答by Deepan Kanugula
USE[Database]
SELECT TABLE_NAME,TABLE_SCHEMA,[Column_Name],[Data_type]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbo'
USE[Database]
SELECT TABLE_NAME,TABLE_SCHEMA,[Column_Name],[Data_type]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dbo'
