Need to return Schema Name in a Query

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  April 29, 2012 at 8:37 am

  #273426

  How can I modify the following script to return the Schema Name?

  SELECT object_name(schema_id) AS SchemaName,

  object_name(IPS.object_id) AS [TableName],

  SI.name AS [IndexName],

  IPS.Index_type_desc,

  IPS.avg_fragmentation_in_percent,

  IPS.avg_fragment_size_in_pages,

  IPS.avg_page_space_used_in_percent,

  IPS.record_count,

  IPS.ghost_record_count,

  IPS.fragment_count,

  IPS.avg_fragment_size_in_pages

  FROM sys.dm_db_index_physical_stats(db_id(N'MyDW'), NULL, NULL, NULL , 'DETAILED') IPS

  JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id

  JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id

  WHERE ST.is_ms_shipped = 0

  ORDER BY 1,2,6

  GO

  I would prefer to sort and or return indexes that are not within an acceptable range.

  Any thought or ideas would be greatly appreciated.

  SQL Server 2008 R2.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Jeffrey Williams

  SSC Guru

  Points: 90350

  More actions

  April 29, 2012 at 9:00 am

  #1480932

  SCHEMA_NAME (http://msdn.microsoft.com/en-us/library/ms175068(v=sql.100).aspx)

  OBJECT_SCHEMA_NAME (http://msdn.microsoft.com/en-us/library/bb326599(v=sql.100).aspx)

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs