-1

In SSMS when I am in a script, I can hover over a column name and it will give me the column's type and if it is Null/Non Null.

Is there a similar simple way to get which table/query in the From clause a column is referenced from (or the alias), if the developer hasn't included it?

For example the simple query below:

SELECT Column1, table1.column2
From 
table1 join 
table2 on table1.id = table2.id

I can see Column2 is referenced from table1, but Column1 doesnt have a reference. If I hover over column1 it might say Column1 (Varchar,Null).

Is there a simple way for SSMS to tell me if column1 is from table1 or table2 like it does its type.

  • It's not pretty, but I think you could get the estimated execution plan and view the xml. Then search for your column in the ColumnReference node. EX: <ColumnReference Database="[Test]" Schema="[dbo]" Table="[Table2]" Column="Column1" /> – Scott Hodgin Sep 26 '19 at 14:53
2

I don't know of an easy implementation in standard SSMS, you could use sp_help / ALT F1 when hovering over the table to check what columns belong to the table.

exec sp_help'dbo.table1'
exec sp_help'dbo.table2'

or selecting the table & pressing ALT+F1

enter image description here

Result

enter image description here

These query shortcuts can be found in options - environment - keyboard - query shortcuts

enter image description here


Other options

You could also look into adding your own custom shortcut in there, or look into external vendors for ssms add ons.

An example of creating your own procedure & shortcut:

USE Database
GO
CREATE PROC dbo.FindIfColumnExists
@TableColumn varchar(255)
AS
SELECT t.name,c.name FROM sys.tables t 
INNER JOIN sys.columns c 
ON t.object_id = c.object_id
WHERE @TableColumn = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'+ QUOTENAME(t.name) + '.' + QUOTENAME(c.name)

With two ways to run it:

EXEC FindIfColumnExists'[dbo].[table1].[Column1]'

or enter image description here

& CTRL + F1

enter image description here

|improve this answer|||||
1

While not native to SSMS, you could download and install the free version of SSMSBoost.

While viewing your query, highlight the column in question, right-click and choose (Locate object in Object Explorer)

enter image description here

The SSMS Object Explorer window will put the focus on the exact table and column.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.