Advertisements

Archive

Posts Tagged ‘sys.tables’

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.

Advertisements

Getting Index count for all tables

August 9, 2010 Leave a comment
WITH cte AS
(
    SELECT   t.name AS Table_Name
	       , t.object_id
		   , i.index_id
		   , i.type
		   , i.type_desc
      FROM sys.indexes i
INNER JOIN sys.tables t
        ON i.object_id = t.object_id
     WHERE t.is_ms_shipped = 0
	   AND i.is_disabled = 0
	   AND i.is_hypothetical = 0
       AND i.type <= 2
), cte2 AS
(
SELECT *
  FROM cte c
 PIVOT (count(type) for type_desc in ([NONCLUSTERED])) pv
)
    SELECT   c2.table_name       AS TableName
           , MAX(p.rows)         AS Rows
		   , CASE WHEN (OBJECTPROPERTY(c2.object_id,'TableHasClustIndex') = 1) THEN
		        'HEAP'
		     ELSE
			    'CLUSTERED'
			 END                 AS TableType
           , sum([NONCLUSTERED]) AS NumOfNonClusteredIndexes
      FROM cte2 c2
INNER JOIN sys.partitions p
        ON c2.[object_id] = p.[object_id]
	   AND c2.index_id = p.index_id
  GROUP BY table_name, c2.object_id

Davide Mauri wrote a nice script, I have done similar work without using CTE.  So I really liked the script; I notice in his comments Rob suggested using OBJECTPROPERTY.  I haven’t used the function much, but just to get use to it I have re-write Davide’s script using Object Property and reformatted the code to my style ;-).   I like Davide’s script, because when it comes to T-SQL coding, I am fairly week.  So it was nice seeing an example double CTE + Pivot table :D.

Reference

  1. SQLBlog.Com. Davide Marui. Find all the tables with no indexes at all. Link.
%d bloggers like this: