2

In a SQL Server environment, while maintaining a SQL query that involves multiple tables, often I'll need to figure out what what table a particular column belongs to.

Unfortunately, the notation in the query is not TableName.ColumnName, but just ColumnName.

Is there any way to look up what table the column is being pulled from, other than manually looking at column definitions for each table? I'm thinking something I can use either in an IDE (ie Management Studio) or a T-SQL statement... but I'm open to almost anything at this point.

One additional point: I need to do this locally, without pushing information over the network, or downloading additional tools outside SQL Management Studio... or Visual Studio.

Aaron Thomas
  • 5,054
  • 8
  • 43
  • 89
  • 1
    Why is the notation `ColumnName` and not `TableName.ColumnName` or at least `TableAlias.ColumnName`? At the latest when two tables have the same columns you have to state the table. – Tim Schmelter Jun 18 '15 at 14:26
  • [I never have this problem in my databases...](http://stackoverflow.com/a/30132058/3094533) – Zohar Peled Jun 18 '15 at 14:28
  • No, there's no easy way. You are doing the job of the query parser. Not only that you have to scan the column names of all tables involved in the `JOINs` but to deal with column aliases, subquery, etc. – Code Different Jun 18 '15 at 14:29
  • if it's a query that runs, the server is able to resolve all references to each of the columns. have you tried looking at the query in the design view instead of the text? – Beth Jun 18 '15 at 14:29
  • @TimSchmelter the column names are not identical between tables. There are so many joins, outer applies to multiple tables, and functions etc... it's difficult to discern which table a column in the midst of everything. – Aaron Thomas Jun 18 '15 at 14:33
  • With Intellisense it seems like reasonable feature. If you mouse over it will tell you the datatype. For any production query I try and use the full name for maintenance. A lot of times people will post questions and you cannot answer property without knowing what table the column belongs to. – paparazzo Jun 18 '15 at 15:16

3 Answers3

2

Well, you can try both of this.

In Management Studio:

  • Mark your query
  • Right click
  • Design Query in Editor

You'll get a graphical interface which will make it easier for you to see which column comes from which table.

Otherwise you can query (if you want to make it automatically) the INFORMATION_SCHEMA:

SELECT c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.TABLE_CATALOG = N'YOURSCHEMA' AND c.TABLE_NAME = N'YOURTABLE'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ionic
  • 3,884
  • 1
  • 12
  • 33
1

If you have Visual Studio installed, and can create a database project, you can use the refactor feature to add fully-qualified names to all SQL queries. This will use aliases if they are employed, but will change the scripts so that every column at least has a reference value.

Scraping the instructions from MSDN to fully qualify all names that are contained in a .sql file in your solution

In Solution Explorer, right-click the script file (.sql) that contains names that you want to update, point to Refactor, and click Fully-qualify Names.

The Preview Changes dialog box appears and shows all names that will be updated.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • This doesn't seem to work with databases deployed without using VS. Even creating a new database project, adding the query (as .sql file), and refactoring, yields only adding schema information, not the name of the parent. This happens if I refactor by right-clicking the database project or the script file. – Aaron Thomas Jun 18 '15 at 14:54
0

For simple queries, I've been able to use the Instant SQL Formatter tool.

Paste your query into the editor, choose List DB Object in the Output dropdown, and click the Format SQL button.

The page will output a list of tables and fields used by the query. The fields will be qualified with the table name, as in your example above.

enter image description here

AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • Good idea... however I'm working with data I don't want to push over the internet. I'll edit my question to clarify. Honestly I didn't think about a solution outside of my workstation. – Aaron Thomas Jun 18 '15 at 14:39
  • That's a fair point, if you had to connect it to data; I've used this to just parse SQL code, which doesn't generally contain any sensitive information. – AHiggins Jun 18 '15 at 14:44