Is there a way to cause SQL Server Management Studio to return query results where the source table names are displayed as prefixes next to each resulting data column name? I'm just manually forming and executing lots of different queries on the fly, queries that bind multiple tables with similarly named columns. I'm doing this to try to learn the ins and outs of an extremely large, undocumented database that has had no relationships established between tables. For instance, table A may have a column named X, but a related table B also has a column named X. I'm finding these similarities as I go, indicating potential relationships between tables. However, when there are several instances of duplicate column names across several large tables I'm trying to bind, it's hard to discern in the query result set whether I'm looking at the X column that comes from table A as opposed to B. I know that I could use the AS keyword and individually relabel my desired columns right in the SELECT clause. And that might be my only recourse if you say so. However, I want to more quickly form queries while I study this database without necessarily making my queries very elaborate right now. It would be great if the SQL Server result sets could have more clearly labeled column headers of the form table.column, for example: A.X, B.X, etc. That would suffice. How can I make SQL Server 2014 display such qualified column headers?
有没有办法让SQL Server Management Studio返回查询结果，其中源表名称显示为每个结果数据列名称旁边的前缀？我只是动态地手动形成和执行许多不同的查询，查询绑定具有类似命名列的多个表。我这样做是为了尝试学习一个非常大的，未记录的数据库的来龙去脉，这个数据库之间没有建立关系。例如，表A可能有一个名为X的列，但是相关的表B也有一个名为X的列。我发现这些相似之处，表明了表之间的潜在关系。但是，当我试图绑定的几个大表中存在多个重复列名的实例时，很难在查询结果集中辨别我是否正在查看来自表A而不是B的X列。我知道我可以使用AS关键字并在SELECT子句中单独重新标记我想要的列。如果你这样说，这可能是我唯一的办法。但是，我想在研究这个数据库时更快地形成查询，而不必立即对我的查询进行详细说明。如果SQL Server结果集可以有更清晰标记的表格table.column的列标题，那将是很好的，例如：A.X，B.X等。这就足够了。如何使SQL Server 2014显示此类限定列标题？
1 个解决方案
#1
0
While I believe that there's no way for SSMS to do what you want in a bonafide automated fashion, there's a way to do so with a smart script, which can build your query without too much frustration. I had the need to do something similar in the past, and I used a variant of the following:
虽然我认为SSMS没有办法以真正的自动化方式做你想要的事情，但是有一种方法可以通过智能脚本来实现，它可以毫不费力地构建你的查询。我过去需要做类似的事情，我使用了以下变体：
select
quotename(tab.name) + '.' + QUOTENAME(col.name) + ' AS ' + quotename(tab.name + '.' + col.name)
from sys.columns col
inner join sys.tables tab on col.object_id = tab.object_id
where tab.name = '<enter table name here>'
This will spit out all fields, properly formatted, quoted, and aliased. With a little tweaking, you should be able to add the rest of your query to this script.
这将吐出所有字段，格式正确，引用和别名。通过一些调整，您应该能够将其余查询添加到此脚本中。
#1
0
While I believe that there's no way for SSMS to do what you want in a bonafide automated fashion, there's a way to do so with a smart script, which can build your query without too much frustration. I had the need to do something similar in the past, and I used a variant of the following:
虽然我认为SSMS没有办法以真正的自动化方式做你想要的事情，但是有一种方法可以通过智能脚本来实现，它可以毫不费力地构建你的查询。我过去需要做类似的事情，我使用了以下变体：
select
quotename(tab.name) + '.' + QUOTENAME(col.name) + ' AS ' + quotename(tab.name + '.' + col.name)
from sys.columns col
inner join sys.tables tab on col.object_id = tab.object_id
where tab.name = '<enter table name here>'
This will spit out all fields, properly formatted, quoted, and aliased. With a little tweaking, you should be able to add the rest of your query to this script.
这将吐出所有字段，格式正确，引用和别名。通过一些调整，您应该能够将其余查询添加到此脚本中。