Finding unused objects

Using the following DMV I get to know which of the objects in the DB is in use
dm_exec_trigger_stats, dm_exec_procedure_stats, dm_db_index_usage_stats
Is there a DMV to have this data about a View?

0
Utf
9/12/2010 8:03:48 AM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
2585 Views

Similar Articles

[PageSpeed] 48

Gal (Gal@discussions.microsoft.com) writes:
> Using the following DMV I get to know which of the objects in the DB is
> in use dm_exec_trigger_stats, dm_exec_procedure_stats,
> dm_db_index_usage_stats 
> Is there a DMV to have this data about a View?

If you want to know which are the unused procedures:

 SELECT o.name
 FROM   sys.objects o
 WHERE  o.type = 'P'
   AND  NOT EXISTS (SELECT *
                    FROM   sys.dm_exec_procedure_stats ps
                    WHERE  ps.database_id = db_id()
                      AND  ps.object_id = o.object_id)

But beware! Just because a procedure is not in the cache does not mean that 
it is unused. It may be a procedure used by an end-of-month report, for 
instance.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
9/12/2010 9:14:41 AM
Reply:

Similar Artilces:

Nothing shows up in Database objects in Database Wizard
Hello, I am having touble with Visio 2003 once again. Though the db wizard, I created an excel spreadsheet (table) for data and am tryinig to link the chart back to this original database so that when I change something in the database it will change correspondingly. I am going back through the DB wizard, and can only get as far as selecting a database object to connect to. I have selected the original dada source, checked table, but nothing showes up under the database objects. Am I or my computer missing something? Please help! Thank You. Have you defined a name for the region ...

Find the row
I have two worksheets one with data and on with a form I would like to find the row that is selected on the data sheet and use it on the form sheet. myRow = ActiveWorkbook.Worksheets("Data").ActiveCell.Row I cannot find a way for this to happen. I have this in a module procedure which I call when the form sheet is activated. any help would be greatly appreciated Anthony The Activecell belongs to a window or the application--not the worksheet. So one way is to go to that worksheet and look at the activewindow: Dim myRow As Long Dim ActCell As Range Dim CurSel A...

how do I access training for OneNote 2010. Can only find 2007
Have just down loaded office 2010. I was going to do the training session for OneNote 2010 but decided to leave it until later. Can anyone direct me to the training exercise. I can only find training for onenote 2007 Thanks I'm not aware of any training available for OneNote 2010 just yet - keep in mind that the 2010 product won't officially be released until June. I'll keep my eyes open though, and post it if I find any. -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.onenote-tips.com "Elainet" <...

Exchange 5.5 System Free/Busy objects left in Exchange 2003 since Migration.
Hi I hope somebody can help with this. I migrated our old Exchange 5.5 Org to Exchange 2003 last year and followed all the MS KB's about doing this. Since then we have switched to Native Mode 2003 and working fine. However we found that our AD infrastructure wasn't at its best and are currently deploying a new Domain. Now this is where the fun starts. While creating a new Exchange 2003 Org in the new Domain, we are replicating the Public/System Folders from the old Org and we have been getting some errors. On investigation we have found that there are redundant Free/Busy Schedule+ obj...

The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address.
Please excuse the rookie question, I have been avoiding Exchange too long and now it has bitten me in the... elbow... I am attempting to set up Exchange to send mail... Whenever I send to a member of my domain (hosted externally) I get this error message... (I am using SBS, I have posted a similar message over there.) The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address. It appears that email sent to other domains is delivered without incident. I need to get the squar...

2002 and Vista
I have created many Access 2002 applications using graphs and they usually work without any problems. I am working on Vista now and having issues with my graphs, amongst other things First, I have to always click the graph to display the data. The data is as simple SQL that uses a limit on the form in which the graph is being displayed to limit the records by. The graphs is not using Linked fields. Whenever I click the graph, the Data Sheet pops up Two questions, how can I have the graph be active all the time without having to click it and prevent the data sheet from showing ? Are th...

Where can I find a template for a perpetual shift schedule.
I am looking for a template that will make it easy for me to schedule several service techs in a week. We work 7 day a week. We work everyday of the year except Christmas. 2 shifts 8:30 am-6:30 pm and 10:30 am to 9:00 pm. I woould like it to be perpetual so that that the template basically auto creates the schedule from month to month. I need this in a way for the schedule to be fair(days off, weekends, holidays etc) maximize number of techs in the field and avoid overtime expenses where possible. I have come up with several versions myself but wondering if there are others I might s...

Finding a server side rule
A server side rule has been created to forward mail from one address to another when it has been recieved. I need to rmove this rule but nobody knows where it was created or by who...is there a way of veiwing all server side rules so I can find and remove it within exchange 2000. Thanks Phill "Phill Heath" <PhillHeath@discussions.microsoft.com> wrote: >A server side rule has been created to forward mail from one address to >another when it has been recieved. > >I need to rmove this rule but nobody knows where it was created or by >who...is there a way o...

Can't find email server
I just purchased Outlook 2007 and can't send or receive any email from my pop3 accounts. I am currently running Vista Home Basic. The "Enter Network Password" window keeps on popping up. Before purchasing Outlook 2007 I was using Microsoft Mail and my Popo3 email accounts were working fine. "lam8231" <lam8231@discussions.microsoft.com> wrote in message news:DAC1E929-2CAA-439C-9421-EE988A3A98CB@microsoft.com... >I just purchased Outlook 2007 and can't send or receive any email from my > pop3 accounts. I am currently running Vista Home Basic. The &q...

Cannot Insert Object
Hi all I'm having trouble inserting a .msg outlook file into an Excel workbook, can anyone please advise as to why I'm getting this error. Many thanks Badger -- BadgerMK ------------------------------------------------------------------------ BadgerMK's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31406 View this thread: http://www.excelforum.com/showthread.php?threadid=511023 ...

Reminder Prompt but Can't Find Entry
I keep being reminded of a recurring calendar entry (weekly appointment) which the program cannot open, find or delete. Yet it each time I open Microsoft Outlook it reminds me of this reccuring entry. Of course there is no way for me to correct the problem and I am told that I may be reminded again since the program cannot locate the entry. Have you tried opening outlook using the /cleanreminders switch? If you aren't sure how to use switches, see http://www.outlook-tips.net/beginner/switches.htm -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips...

Lock an object to a slide
Can anyone tell me how to lock an object to a slide in PowerPoint? For example, if I'm inserting multiple objects, once I have the first one in the place I want it, can I tell PowerPoint not to let it move from that place? Thanks!! :) I'm turning on the ESP and guessing here because you're not telling us much! Maybe, just mayb,e Tools > Auto Correct options > Autoformat as You Type > UNTICK auto layout for inserted objects?? -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutoria...

Finding an item in a listview
There has to be a way to find a specific item in a specific column in a listview. I have a list view of people and then I allow the user to pop up a dialog box that allows them to enter search text into a field and it finds all the records that qualify and displays them in a listctrl inside the dialog box and then I want them to be able to click on the record they want and go to that record in the main list view. Yes. Using GetItemText, you can examine the contents of any listview item. Simply iterate through the items and compare each one. In article <OOW30AriDHA.560@tk2msftngp13.p...

Storing objects in Session variables
At http://www.eggheadcafe.com/articles/20021016.asp I read that: "If you are using InProc mode, objects stored in session state are actually live objects, and so you can store whatever object you have created." This wasn't true in classic ASP; is it true in ASP.NET? What issue are you referring to ? It's bit hard to give a "yes" response without any context. For example being able to store a connection doesn't mean you should (and it has not changed in ASP.NET)... -- Patrice "Harlan Messinger" <h.usenetremoverthis@gavelcade....

How to Access description of a Form object
Hi Folks, I need some help in VBA coding.. I have an application in which my backend data is stored in SQL Server 2005 . Front end has been written in MS-Access 2002, so it is obvious that the front end is an Access Database which contains forms, reports and queries. My current task at hand: There are forms and sub-forms in the front end database. Only the form objects and report objects have "description" on them apart from the "form.Name". I need to write the code snippet to read the front-end database and display the form and report Descriptions of only those object...

vb6 run time error 5 when Outlook 2007 Explorer object displayed
hi all I have a vb6 program which integrates with MS Outlook and was working fine until the client upgraded their PC's - the OS stayed at XP Pro but Office was upgraded from 2003 to 2007. The app is now throwing a runtime error 5 "invalid procedure call or argument". This is difficult for me to debug as I my dev machine is W2K so I cannot install Office 2007 on it. But as far as I can tell the error is occurring when the code gets to the line myExplorer.Display - this is in response to the user double clicking a calendar item in Outlook. Just wondering is...

Can not find Links
In the edit, links dialog box it says that I have a link to an external spreadsheet. I have searched all macros, defined names and formulas with no success. I can not find the link....any suggestions?? Hi try the following add-in to find/delete these links: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink.zip -- Regards Frank Kabel Frankfurt, Germany "DL" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:13af801c44424$eb36b900$a401280a@phx.gbl... > In the edit, links dialog box it says that I have a link > to an external spreadsheet. I have sea...

Instant Search, Advanced Find, Search Folders, and Windows Desktop
Instant search stopped working for me. Now if I search for even the letter "a", I get the following message: No matches found for: "a". When I ask "advanced find" to find any emails with categories contains TNE01, it returns no results. However, advanced find works somewhat. For example when I look for items in my inbox with "a" in the subject line, it returns 78 matches. When I use "search folders", to find emails with categories = TNE01, 14 items are returned. When I use "search folders" to find items with "WC" in freq...

how do I connect an object from one page to a different page?
I am trying to connect an object from one page to a seperate object on a different page. The instructions say to hold down your mouse button then click on the point you want it connected too. My problem is that is doesn't allow you to select the starting point of the connector, then select a different page, then click on your end point of the connector. Please help! try using a hyperlink. al "Pepper" <Pepper@discussions.microsoft.com> wrote in message news:3F5657F4-48EA-4681-A21E-D33FC7030401@microsoft.com... >I am trying to connect an object from one page to...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Finding my product key
I have a copy of Publisher 2003, which I have installed on one computer and I would like to install it again. I have the disk but I can't find the case for it so I don't have the product code. How can I find out the number from the first computer to apply to the second computer. I think you may be SOL on this one. Unless the second computer is a laptop, you are not permitted to install it on an additional system. You'll need to contact Microsoft to obtain an additional product key if the installation is on a laptop. -- Ron "Don" <Don@discussions.microsoft.co...

Clear all including objects without deleting sheets
I would like a macro that uses the "EDIT" - "CLEAR ALL" command for all sheets that do not have colored tabs. I would also like it to remove all charts, objects, images, etc. for all sheets that do not have colored tabs. Currently, I can only remove all objects by manually deleting each object one at a time. Deleting the sheets is not an option because that results in cell refrences for me as I refrence cells in the sheets that I am trying to clear. Any help is greatly appreciated! Curt J try this Sub clearall() On Error Resume Next For Each ws In ...

sorry, I could not find any host named <domain name> (#5.1.2)
I upgraded to Windows 2003 and Exchange 2003 over the weekend. Now, I can not send email to yahoo accounts and when sending from yahoo back, I get the above message. I do not have a "smarthost" setup and am running DNS on the exchange as well. I went to www.dnsreport.com and it says all is fine. Any ideas with what is going on with my SMTP? Setting wrong? TIA, Darrin hi darrin! either check your DNS snapin, in the properties of your dns server if you have set dns forwarders. use at least one external dns, e.g. the dns of your isp. or you open the properties of you smtp vir...

Publisher can't find Outlook
Both Outlook 2007 and Publisher 2007 are installed, Outlook is the only email- application in use. When I want to emerge a newsletter from Publisher to Outlook, it keeps telling me to install and configure Outlook 2007. I cannot find any solution to this problem. Can anyone help me? In Outlook, open help, look at the eighth entry. -- Mary Sauer http://msauer.mvps.org/ "Jannieke" <u49099@uwe> wrote in message news:90bada5a3a2a0@uwe... > Both Outlook 2007 and Publisher 2007 are installed, Outlook is the only email- > application in use. When I want to emerge a newslett...

Excel Find Replace in batches
The standard Find & Replace tool in excel allows only a single item to replace another single item. I am looking for help on how to "batch" replace, i.e. replace all instances of January, February, March with Jan, Feb, Mar. I need to do this against thousands and thousands of entries. Does anyone have a way of referencing a "library" of master replacements and applying them to an immense worksheet of text values at once? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from h...