gpt4 book ai didi

sql - 数据库中所有列的逗号分隔列表(表名 | 列名...)

转载 作者:行者123 更新时间:2023-12-02 10:36:26 27 4
gpt4 key购买 nike

在 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....)

是否可以通过WHILE LOOP或其他方式得到上述结果?如果是,您可以发布代码吗?

此外,我尝试使用临时表来解决此问题:

 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

我成功地垂直列出了列,但无法获得逗号分隔的列列表。

最佳答案

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

正如评论中提到的,以上内容将包含 View 。如果您想排除 View ,可以执行以下操作:

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

关于sql - 数据库中所有列的逗号分隔列表(表名 | 列名...),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4936634/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com