Problem
When reviewing code in SQL Server Management Studio (SSMS) is there a quick way to see table details and attributes? Often, we want to know more about the tables used in SPs or scripts when reviewing or troubleshooting. We might want to look at the table columns and datatypes or maybe we want to see if the table has an Identity column. It would be nice to have a quick interactive way to get table details when reviewing SQL scripts, stored procedures and other object code.
Solution
This tip is in the category of simple things I do every day. I will show the SSMS shortcut keys for running sp_help, to see table details when reviewing scripts, stored procedure, views and other SQL code. I will go more in depth on the sp_help system store procedure behind the shortcut keys. Last, I will show an alternative way to see details about a table, using Object Explorer. In the examples I will use the sample database AdventureWorks.
1. Quickly See Table Details with Alt + F1 Query Shortcut
When reviewing SQL code, stored procedures, or views in SSMS, often I use the SSMS built in shortcut Atl+F1 to show table attributes.
First be sure you are connected to SQL Server in SSMS and have your query window set to the database where the tables in the script reside.
For example, if working with the query below and you wanted to learn more about the Person.Address table, like all the columns and indexes, then highlight the schema.table and hit Alt+F1.
Quickly, you see a lot of table details as shown below.
This method will show the table create date, all columns and column information, Identity property column, RowGuidCol, Filegroup, Index list with the non-included columns, Constraints and Keys, and Table references by Foreign Keys.
A disclosure: index info does not show the include list of included columns in indexes, rather only the columns in the index. Maybe one day Microsoft will add the included columns in the index section.
This is the fastest way to get to table details. This also works with Stored Procedures showing the parameters and on Views showing the columns and other relevant information.
Note, if the object is in the dbo schema you can highlight just the object name and hit Alt+F1, but if the object is under a named schema you must highlight both the schema.objectname! Also, when highlighting the schema and table name ensure there are no additional white spaces highlighted.
2. See Table Details Using System Stored Procedure sp_help
Using the Alt+F1 shortcut key essentially calls sp_help 'schema.tablename', and we can use this stored procedure directly from an SSMS Query Window as shown below.
--Show all table info exec sp_help 'Person.Address';
This method will show the same results as what we see above.
3. See Table Details in Object Explorer in SQL Management Studio
In SSMS, another common way to explore details about a table is to expand the Table in the Object Explorer window and then expand the attribute Folders under the Table to see the attributes that you want to see. This is a slower method and it does not allow you to see all the details quickly without expanding each table attribute. You can see details including Columns, Keys, Constraints, Triggers, Indexes and Statistics.
An example of this is shown in the below image. This is a good method but not always the most practical if you are reviewing TSQL code in an SSMS Query Window and want to quickly see table attributes of various tables in the script. In that case using the Alt+F1 shortcut key is the fastest method. You might use Object Explorer method if you want to see more index details, like the included columns.
Below shows the Person.Address table with all the attributes expanded.
You can also use the Object Explorer method if you want to script out any table attributes to a query window, See below where I show scripting out the Foreign key FK_Address_StateProvince_StateProvinceID to a new Query Window.
4. More on Query Shortcut
To see how the Alt+F1 is configured in SSMS, this can be found under Tools, Options, expand Keyboard, and see Query Shortcuts. See the other default shortcuts or configure your own custom shortcuts.
Wrap Up
Under the subject of “simple things I do everyday”, using Alt+F1 on tables is one I use frequently to review scripts or Stored Procedures. Often, I’m looking for Clustered Keys, column datatypes or other information. Sometimes when working with others and sharing my screen, I’ll quickly use Alt+F1 on a table and the person I’m working with will ask how I did that. I usually reply that Alt+F1 is your friend.
Next Steps
MSSQLTips has several articles on SQL Management Studio Shortcuts. Look at a few of these:
