Geeks With Blogs
Jim Giercyk

So, you are asked to find out which table uses up the most index space in a database.  Or how many rows are in a give table.  Or any other question about the metadata of the server.  Many people will immediately jump into writing a T-SQL or Powershell script to get this information.  However, SSMS can give you all the information you need about any object on the server.  Enter, OBJECT EXPLORER DETAILS.  Simply go to View –> Object Explorer Details OR hit F7 to open the window.  Click on any level of the Object Explorer tree, and see all of the objects below it.  So, lets answer the first question above…..which table uses the most index space.  We’ll check the AdventureWorks2012 database and see:

 


image


 

Dammit Jim, that doesn’t tell us anything!  Well, hold on Kemosabe, there’s more.  Just like every other Microsoft tool, you can right-click in the column bar of the Object Explorer Details pane, and add the column[s] you want to see.  So, I’ll right-click on the column bar and select the “Index Space Used” column.  The columns can be sorted by clicking on the column name.  So, that makes our job a lot easier:

 


 

image

 


 

And, as we might have guessed, the dreaded SalesOrderDetail table uses the most Index Space.  And, we’ve found that out without writing a single line of code, and we can be sure the results are accurate.  I guess it’s possible that you are never asked about your metadata.  Maybe you’re thinking “That don’t confront me, as long as I get my paycheck next Friday” (George Thorogood reference….I couldn’t help it).  But wait, don’t answer yet……did I mention that the OED window can help us with bulk scripting?  Let’s say we want to make changes to several stored procedures, and we want to script them off before doing so.  We have a choice to make:

  • Right-click on each procedure and choose “Script Stored Procedure As”
  • Right-click on the database, select Tasks –> Generate Scripts, and then use the wizard to whittle down the list of objects to just the stored procedure you want
  • Highlight the stored procedures you want to script in the OED window and right-click, Script Stored Procedure As.

 


 

image

 


 

Yeah, ok that’s all well and good, but there really isn’t much time savings between the 3 options in that scenario.  I’ll concede that point, but consider this; Without using the OED window, there is no way to script non-database objects like Logins, Jobs, Alerts, Extended Events, etc, etc, etc without right-clicking on each individual object and choosing “Script As”.  In the OED, just highlight all the objects you want to script and right-click.  You’re done.  If you have 50 users you need to migrate to a new instance, or you want to move all of your maintenance jobs to a new server, that’s going to save a lot of time.

 

I’ve seen lots of people much smarter than I am go hog-wild writing scripts to answer simply questions like “which table has the most rows”.  Or, where is the log file from that table stored in the file system (you can find that out too).  Or what are the database collations on your server?  I’ve seen them opening multiple windows and cutting and pasting objects onto a new server.  While they were busy coding and filling in wizards, I hit F7 and got the job done in a few seconds.  Work smarter, not harder.  I hope this helps you in some way….thanks for reading.

Posted on Thursday, May 11, 2017 11:41 AM | Back to top


Comments on this post: Getting SQL Server MetaData the EASY Way Using SSMS

# re: Getting SQL Server MetaData the EASY Way Using SSMS
Requesting Gravatar...
VERY NICE POST ADMIN THANKS.mobdro for Windows
xender download app
Left by mobdro tv app for pc on May 13, 2017 3:18 AM

# Great post
Requesting Gravatar...
Awesome and helpful post! Thanks for sharing this post. Nice tips shared. chennai to shirdi flight package, chennai to shirdi tour package

https://www.youtube.com/watch?v=7pz901-J4IA
Left by Sri Sairam Subhayatra on May 16, 2017 5:00 AM

# re: Getting SQL Server MetaData the EASY Way Using SSMS
Requesting Gravatar...
Hi,
This was great post on SQL Server MetaData and SSMS.
Nice information.
Left by accurate on May 19, 2017 5:23 AM

# re: Getting SQL Server MetaData the EASY Way Using SSMS
Requesting Gravatar...
Awesome and helpful post! Thanks for sharing this post. http://www.agenbolatangkas.info
Left by Tangkas Asia on May 21, 2017 3:50 PM

# re: Getting SQL Server MetaData the EASY Way Using SSMS
Requesting Gravatar...
I work as a data scientist and these posts are really useful to me. I also happen to run a Marketing Lifestyle Blog and a Travel Itinerary and this might come in handy someday
Left by Karthik Murali H on May 23, 2017 3:11 PM

# re: Getting SQL Server MetaData the EASY Way Using SSMS
Requesting Gravatar...
Using SSMS many things like getting the SQL server Metadata are made easier and I found the things related to this discussed in this article very informative. I was searching for the explanations that support such things and keep your great work!
wholesale tablets
Left by Merlin John on May 24, 2017 7:54 AM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net