August 27, 2014 at 8:41 am
I am working on a project where the naming conventions are not strictly followed. Developers have used a lot of views in their SQL queries. You can not tell the if it is a table or a view just from the name of the object.
For example:
Select Col1, Col2
From Product
Inner Join
OrderDetails
on Product.Col1 = OrderDetails.Col1
In this query, Product is a table while OrderDetails is a view. You can not simply tell that without querying sys.objects table or looking table/view list in Management Studio.
I am wondering if there is a better way/functions in Management Studio (or SQL) to distinguish tables from views in a query. For example, jump in to view the definition if it is a view.
Many thanks!
August 27, 2014 at 9:08 am
You can select the table/view name and hit Alt + F1. This is the keyboard shortcut to run sp_help for that object.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2014 at 9:22 am
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
John
September 18, 2014 at 7:34 am
Thanks both. Exactly what I am looking for!
September 18, 2014 at 1:39 pm
Or:
SELECT OBJECTPROPERTYEX ( OBJECT_ID('object_name') , 'IsView' ) AS IsViewOrNot
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 18, 2014 at 1:43 pm
When I hover over the object name in SSMS it tells me table or view in the query window. Not sure if I have something extra turned on ?
***SQL born on date Spring 2013:-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply