Advertisements

Archive

Posts Tagged ‘OBJECT_NAME’

Index_Name Function for SQL Server

June 18, 2012 1 comment

We have the database_name, object_name, object_schema_name but don’t have index_name.  So when accessing information in sys.dm_db_index_usage stats, it becomes troublesome to find out index name for al indexes.

Starting SQL 2008+ they updated the object_name function.  Before it used to be object_name(object_id) in context of current database; however the new function is object_name(object_id,[database_id]).  If they can do that for object_name & schema_name, why not indexes?

Searching Microsoft Connect, found the recommendation for it (link). Microsoft closed the feedback saying its too late for 2008, it will be looked at next version.  Well 2012 is out, I still don’t see it.  But I was thinking we we can still vote on it “maybe” it will get more attention?

Advertisements

Last updated date for statistics?

November 9, 2011 Leave a comment

There is many ways to get his information; however in most cases you get information for a single table. If you want to get it for all statistics in a database, how to do it? We can use the STATS_DATE function to get this information. Here is a simple query …

SELECT  OBJECT_NAME(s.OBJECT_ID)         AS TableName
      , s.name                           AS StatisticsName
      , STATS_DATE(s.OBJECT_ID,stats_id) AS LastUpdated
 FROM sys.stats s
 JOIN sys.tables t ON s.OBJECT_ID = t.OBJECT_ID

You can use this query to control what stats to build based on whent hey were last built.  With the change windows being small in some organization, you might not be able to rebuild all stats every week.  Specially for larger tables with large sample size; in which case basing your builds on Last Updated date will be helpful.

%d bloggers like this: