/****** Object:  View [dbo].[ng_indices_current_vw]    Script Date: 1/20/2015 9:22:59 AM ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[dbo].[ng_indices_current_vw]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[ng_indices_current_vw]
GO

/****** Object:  View [dbo].[ng_indices_current_vw]    Script Date: 1/20/2015 9:22:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create view [dbo].[ng_indices_current_vw]
as
--{5.8.1.3}

---------------------- 
-- $Change: 255139 $ 
-- $DateTime: 2013/10/30 10:09:02 $ 
-- $Author: ptrombetta $
----------------------
/*****************************************************************************************************
5.5.29.24  	05/13/2007	rmc     add view to every query to impove the performance
						fix DI processing, index wasn't recreating
                                			filtered sys tables and non critical NG tables
5.5.22.23 	05/13/2007  	rmc     add columns for stats update sample rate , full text searches, oracele iot and function indexes
5.5.25.2    	01/13/2008  	rmc     excludes ncs tables
5.5.27.3   	04/10/2008   	rmc     verify 5.6 sync
5.5.28.6   	04/10/2008   	rmc     fix file group
5.6.3.7   	04/10/2008   	rmc	fix file group
5.7.2.38		 12/08/2011	rl	added included columns for sql 2005 + using fn_get_included_columns
5.8.0.59		 10/01/2012	jxm	excluded dash_ tables for dash 3.x
5.8.0.106		10/26/2012	rl	removed index id limitation
5.8.1.3		 09/25/2013	DPW	   Changed tables to sys.indexes and sys.objects due to table deprication of sysindexes/sysobjects
******************************************************************************************************/

SELECT  so.name AS table_name,
	    si.name AS index_name,
	    dbo.fn_get_index_columns(so.object_id, si.index_id) AS index_columns, 
	   	dbo.fn_get_included_columns(so.object_id, si.index_id) AS 'included_columns', 
				CASE WHEN filegroup_name(data_space_id) = 'Primary' THEN 'NEXTGEN_INDEX_1' 
					ELSE filegroup_name(data_space_id)
					END AS group_name, 
                CASE WHEN (si.is_primary_key = 1) THEN 'Y' 
					ELSE 'N' 
					END AS primary_ind, 
				CASE WHEN (si.type = 1) THEN 'Y'
					ELSE 'N' 
					END AS clustered_ind, 
                CASE WHEN (si.is_unique) <> 0 THEN 'Y' 
					ELSE 'N' 
					END AS unique_ind, 
				CASE WHEN OBJECTPROPERTY(so.object_id, 'TableFulltextCatalogID') <> 0 THEN 'Y' 
					ELSE 'N' 
					END AS fulltext_index_ind, 
                CASE WHEN so.name LIKE '%mstr' THEN 100 
					ELSE 40 
					END AS stat_rate, 
				'N' AS iot_table_ind, 
				'N' AS functionbase_ind

FROM sys.indexes AS si WITH(NOLOCK) 
	INNER JOIN sys.objects AS so WITH(NOLOCK) ON si.object_id = so.object_id

WHERE	  si.index_id > 0
	  AND si.is_hypothetical = 0
	  AND so.type = 'U'
	  AND (so.name NOT LIKE 'sysdiagrams') 
	  AND (so.name NOT LIKE 'dtproperties') 
	  AND (so.name NOT LIKE 'sys.%') 
	  AND (so.name NOT LIKE 'upg_FusionColumns') 
	  AND (so.name NOT LIKE 'ncs[_]%') 
	  AND (so.name NOT LIKE 'fv[_]%')
	  AND (so.name NOT LIKE 'dash[_]%')

GO


