SQL 数据库中所有列的逗号分隔列表 (Tablename | Column_names...)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4936634/
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
Comma Separated List of all columns in the Database (Tablename | Column_names...)
提问by CricMan
In SQL Server, I would like see Table_Name and all the Columns associated with that Table_Name in a database. So the Output should look like this:
在 SQL Server 中，我希望在数据库中看到 Table_Name 以及与该 Table_Name 关联的所有列。所以输出应该是这样的：
TABLE_NAME COLUMN_NAME
1. Employee Employee-id, Lastname, Firstname, Title...........
2. Orders Orderid, Order-date, shipped-date, delivery-date.......
3. Products Product-id, Product-name, supplier-id, category-id.....
4. Suppliers Supplier-id, Company-name, contact-name.......
5. ............................................................
6. ...................................................
(So on....)
Is it possible to get the above results with WHILE LOOP or any other way? If YES, could you post the code.
是否可以使用 WHILE LOOP 或任何其他方式获得上述结果？如果是，你能发布代码吗？
Also, I tried to do this problem using a Temp Table:
另外，我尝试使用临时表来解决这个问题：
create table #hello
(table_name1 Varchar(max))
insert into #hello(table_name1)
select table_name from information_schema.columns
GO
create table #hello2
(table_name2 varchar(max),column_name2 varchar(max))
insert into #hello2(table_name2 ,column_name2)
select table_name,column_name from information_schema.columns
GO
select a.table_name1,b.column_name from #hello a inner join
information_schema.columns b
on a.table_name1=b.table_name COLLATE Latin1_general_CI_AS
order by table_name
GO
I was successful in listing the columns Vertically but i couldn't get the comma separated list of columns.
我成功地垂直列出了列，但我无法获得以逗号分隔的列列表。
回答by Thomas
Select TABLE_SCHEMA, TABLE_NAME
, Stuff(
(
Select ', ' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 2, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
As mentioned in comments, the above will include views. If you want to exclude views you can do the following:
如评论中所述，以上将包括意见。如果要排除视图，可以执行以下操作：
Select T.TABLE_SCHEMA, T.TABLE_NAME
, Stuff(
(
Select ', ' + C.COLUMN_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
And C.TABLE_NAME = T.TABLE_NAME
Order By C.ORDINAL_POSITION
For Xml Path('')
), 1, 2, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
Left Join INFORMATION_SCHEMA.VIEWS As V
On V.TABLE_SCHEMA = T.TABLE_SCHEMA
And V.TABLE_NAME = T.TABLE_NAME
Where V.TABLE_NAME Is Null
回答by Martin Smith
select name as TABLE_NAME,
STUFF(COLUMN_NAME, 1, 1, '') AS COLUMN_NAME
from sys.tables t
CROSS APPLY
(
SELECT
',' + name AS [text()]
FROM
sys.columns c
WHERE
c.object_id = t.object_id
FOR XML PATH('')
) o (COLUMN_NAME)
回答by Mark
Since your question mentions 'in a database', rather than use the information schema, I used the database:
由于您的问题提到“在数据库中”，而不是使用信息模式，我使用了数据库：
select t.name,
STUFF ((
select ',' + c.name
from TEST_DB.sys.columns c
join TEST_DB.sys.tables tt on tt.object_id = t.object_id and t.object_id = c.object_id
join TEST_DB.sys.schemas s on tt.schema_id = s.schema_id
order by t.name, c.column_id
for xml path('')), 1, 1, '') as columns
from TEST_DB.sys.tables t
where t.name = 'PutTableNameHere if you want to filter'
Leave the where clause out for all tables in a specific database.
为特定数据库中的所有表保留 where 子句。
