SELECT
	CASE
		WHEN LEN(i.[index]) = 0 AND LEN(i.[column]) = 0 THEN i.[table]
		ELSE ''
	END AS [table],
	i.[row count],
	CASE
		WHEN LEN(i.[column]) = 0 THEN i.[index]
		ELSE ''
	END AS [index],
	CASE
		WHEN LEN(i.[column]) = 0 THEN i.[primary key]
		ELSE ''
	END AS [primary key],
	CASE
		WHEN LEN(i.[column]) = 0 THEN i.[clustered]
		ELSE ''
	END AS [clustered],
	CASE
		WHEN LEN(i.[column]) = 0 THEN i.[unique]
		ELSE ''
	END AS [unique],
	CASE
		WHEN LEN(i.[column]) = 0 THEN i.[fill factor]
		ELSE ''
	END AS [fill factor],
	CASE
		WHEN LEN(i.[column]) = 0 THEN i.[disabled]
		ELSE ''
	END AS [disabled],
	i.[column],
	i.[data type],
	i.nullable,
	i.[identity],
	i.[included column]
FROM
	(
		-- table information
		SELECT
			s.[name] AS [schema],
			CASE
				WHEN s.[name] = 'dbo' THEN t.[name]
				ELSE s.[name] + '.' + t.[name]
			END AS [table],
			CAST(SUM(p.[rows]) AS VARCHAR(MAX)) AS [row count],
			'' AS [index],
			'' AS [column],
			'' AS [included column],
			'' AS [primary key],
			'' AS [clustered],
			'' AS [unique],
			'' AS [fill factor],
			'' AS [disabled],
			0 AS key_ordinal,			
			0 AS index_column_id,
			'' AS [data type],
			'' AS nullable,
			'' AS [identity]
		FROM
			sys.schemas s
				INNER JOIN sys.tables t ON
					s.[schema_id] = t.[schema_id]
				INNER JOIN sys.partitions p ON
					p.[object_id] = t.[object_id]
		WHERE
			t.is_ms_shipped = 0 AND
			NOT EXISTS
			(
				SELECT *
				FROM
					sys.extended_properties ep
				WHERE
					ep.major_id = t.[object_id] AND
					ep.minor_id = 0 AND
					ep.class = 1 AND
					ep.[name] = 'microsoft_database_tools_support'
			) AND
			p.index_id < 2
		GROUP BY
			s.[schema_id],
			s.[name],
			t.[object_id],
			t.[name]
					
		UNION ALL

		-- top level index information
		SELECT
			s.[name] AS [schema],
			CASE
				WHEN s.[name] = 'dbo' THEN t.[name]
				ELSE s.[name] + '.' + t.[name]
			END AS [table],
			'' AS [row count],
			i.name AS [index],
			'' AS [column],
			'' AS [included column],
			CASE
				WHEN i.is_primary_key = 1 THEN 'Y'
				ELSE ''
			END AS [primary key],
			CASE
				WHEN i.type_desc = 'CLUSTERED' THEN 'Y'
				ELSE ''
			END AS [clustered],
			CASE
				WHEN i.is_unique = 1 THEN 'Y'
				ELSE ''
			END AS [unique],
			CASE
				WHEN i.is_padded = 1 THEN CAST(i.fill_factor AS VARCHAR(MAX))
				ELSE ''
			END AS [fill factor],
			CASE
				WHEN i.is_disabled = 1 THEN 'Y'
				ELSE ''
			END AS [disabled],
			0 AS key_ordinal,
			0 AS index_column_id,
			'' AS [data type],
			'' AS nullable,
			'' AS [identity]
		FROM
			sys.schemas s
				INNER JOIN sys.tables t ON
					s.[schema_id] = t.[schema_id]					
				INNER JOIN sys.indexes i ON
					i.[object_id] = t.[object_id]
		WHERE
			i.is_hypothetical = 0 AND
			i.type_desc <> 'HEAP' AND
			t.is_ms_shipped = 0 AND
			NOT EXISTS
			(
				SELECT *
				FROM
					sys.extended_properties ep
				WHERE
					ep.major_id = t.[object_id] AND
					ep.minor_id = 0 AND
					ep.class = 1 AND
					ep.[name] = 'microsoft_database_tools_support'
			)

		UNION ALL

		-- index column information
		SELECT 
			s.[name] AS [schema],
			CASE
				WHEN s.[name] = 'dbo' THEN t.[name]
				ELSE s.[name] + '.' + t.[name]
			END AS [table],
			'' AS [row count],
			i.name AS [index],
			c.name +
				CASE
					WHEN ic.is_descending_key = 1 THEN ' DESC'
					ELSE ''
				END AS [column],
			CASE
				WHEN ic.is_included_column = 1 THEN 'Y'
				ELSE ''
			END AS [included column],
			CASE
				WHEN i.is_primary_key = 1 THEN 'Y'
				ELSE ''
			END AS [primary key],
			CASE
				WHEN i.type_desc = 'CLUSTERED' THEN 'Y'
				ELSE ''
			END AS [clustered],
			CASE
				WHEN i.is_unique = 1 THEN 'Y'
				ELSE ''
			END AS [unique],
			CASE
				WHEN i.is_padded = 1 THEN CAST(i.fill_factor AS VARCHAR(MAX))
				ELSE ''
			END AS [fill factor],
			CASE
				WHEN i.is_disabled = 1 THEN 'Y'
				ELSE ''
			END AS [disabled],
			ic.key_ordinal,
			ic.index_column_id,
			CASE
				WHEN uty.[name] IS NOT NULL THEN uty.[name]
				ELSE ''
			END +
				CASE
					WHEN uty.[name] IS NOT NULL AND sty.[name] IS NOT NULL THEN '('
					ELSE ''
				END +
				CASE
					WHEN sty.[name] IS NOT NULL THEN sty.[name]
					ELSE ''
				END +
				CASE
					WHEN sty.[name] IN ('char', 'nchar', 'varchar', 'nvarchar', 'binary', 'varbinary')
						THEN '(' + 
							CASE
								WHEN c.max_length = -1 THEN 'max'
								ELSE 
									CASE
										WHEN sty.[name] IN ('nchar', 'nvarchar')
											THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
										ELSE
											CAST(c.max_length AS VARCHAR(MAX))
									END
							END
							 + ')'
					WHEN sty.[name] IN ('numeric', 'decimal')
						THEN '(' + 
							CAST(c.precision AS VARCHAR(MAX)) + ', ' + CAST(c.scale AS VARCHAR(MAX))
							 + ')'
					ELSE
						''
				END +
				CASE
					WHEN uty.[name] IS NOT NULL AND sty.[name] IS NOT NULL THEN ')'
					ELSE ''
				END	AS [data type],
			CASE
				WHEN c.is_nullable = 1 THEN 'Y'
				ELSE ''
			END AS nullable,
			CASE
				WHEN c.is_identity = 1 THEN 'Y'
				ELSE ''
			END AS [identity]
		FROM 
			sys.schemas s
				INNER JOIN sys.tables t ON
					s.[schema_id] = t.[schema_id]					
				INNER JOIN sys.indexes i ON
					i.[object_id] = t.[object_id]
				INNER JOIN sys.index_columns ic ON
					ic.index_id = i.index_id AND
					ic.[object_id] = t.[object_id]
				INNER JOIN sys.columns c ON
					c.column_id = ic.column_id AND
					c.[object_id] = t.[object_id]

				-- get name of user data type
				LEFT OUTER JOIN sys.types uty ON
					uty.system_type_id = c.system_type_id AND
					uty.user_type_id = c.user_type_id AND
					c.user_type_id <> c.system_type_id

				-- get name of system data type
				LEFT OUTER JOIN sys.types sty ON
					sty.system_type_id = c.system_type_id AND
					sty.user_type_id = c.system_type_id
		WHERE
			i.is_hypothetical = 0 AND
			t.is_ms_shipped = 0 AND
			NOT EXISTS
			(
				SELECT *
				FROM
					sys.extended_properties ep
				WHERE
					ep.major_id = t.[object_id] AND
					ep.minor_id = 0 AND
					ep.class = 1 AND
					ep.[name] = 'microsoft_database_tools_support'
			)
	) i
ORDER BY
	i.[schema],
	i.[table],
	CASE
		WHEN LEN(i.[index]) = 0 THEN 1
		ELSE 2
	END,
	i.[index],
	CASE
		WHEN LEN(i.[column]) = 0 THEN 1
		ELSE 2
	END,
	CASE
       WHEN i.[included column] = 'Y' THEN 2
       ELSE 1
	END,
	i.key_ordinal,
	i.index_column_id;