working on it ...

Filters

Explore Public Snippets

Sort by

Found 14k snippets matching: information

    public by cghersi  327999  0  6  1

    Retrieve useful information regarding indexes in MS SQL Server

    This script helps the DBA to retrieve valuable information regarding indexes. Here the complete explanation of the output fields: object_type: type of object (will either be "U" for User-Defined Table or "V" for View) database_name: name of the database schema_name: name of the schema object_name: name of the object create_date: date the obje
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    SET ARITHABORT OFF
    SET ARITHIGNORE ON
    SET TEXTSIZE 2147483647
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Declarations / Sets: Declare And Set Variables
    -----------------------------------------------------------------------------------------------------------------------------
    
    DECLARE @Database_ID AS SMALLINT
    
    
    SET @Database_ID = DB_ID ()
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Index Key, Include Key, And Filter Definition Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 sqI.[object_id]
    	,sqI.index_id
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.index_key), 1, 2, N'') AS index_key
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.include_key), 1, 2, N'') AS include_key
    	,sqI.filter_definition
    	,RANK () OVER
    				(
    					ORDER BY
    						 sqI.[object_id]
    						,CONVERT (NVARCHAR (MAX), sqI.index_key)
    						,sqI.filter_definition
    				) AS dupe_rank
    INTO
    	dbo.#temp_index_breakdown_keys_filters
    FROM
    
    	(
    		SELECT
    			 I.[object_id]
    			,I.index_id
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END)
    					+ N' '
    					+ (CASE
    							WHEN IC.is_descending_key = 0 THEN N'[A]'
    							WHEN IC.is_descending_key = 1 THEN N'[D]'
    							ELSE N'[N/A]'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 0
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS index_key
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 1
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS include_key
    			,I.filter_definition
    		FROM
    			sys.indexes I
    	) sqI
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Size Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 DDPS.[object_id]
    	,DDPS.index_id
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.row_count
    			END) AS [rows]
    	,SUM (DDPS.reserved_page_count) AS total_pages
    	,SUM (DDPS.used_page_count) AS used_pages
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			ELSE DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			END) AS data_pages
    INTO
    	dbo.#temp_index_breakdown_size_info
    FROM
    	sys.dm_db_partition_stats DDPS
    GROUP BY
    	 DDPS.[object_id]
    	,DDPS.index_id
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Main Query: Final Display / Output
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 (CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type]
    		ELSE ''
    		END) AS object_type
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN DB_NAME ()
    		ELSE ''
    		END) AS [database_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN SCHEMA_NAME (sqBAQ.[schema_id])
    		ELSE ''
    		END) AS [schema_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN OBJECT_NAME (sqBAQ.[object_id])
    		ELSE ''
    		END) AS [object_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.create_date
    		ELSE ''
    		END) AS create_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN caMDKL.modify_date
    		ELSE ''
    		END) AS modify_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqBAQ.[rows]), 1)), 4, 22))
    		ELSE ''
    		END) AS [rows]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.total_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS total_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.used_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS used_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.unused_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS unused_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.data_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS data_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.index_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS index_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.data_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_data
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.index_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_index
    	,sqBAQ.type_desc AS index_type
    	,ISNULL (sqBAQ.index_name, '') AS index_name
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		WHEN sqBAQ.is_primary_key = 0 AND sqBAQ.is_unique = 0 THEN REPLICATE ('.', 6)
    		WHEN sqBAQ.is_system_named = 0 THEN 'No'
    		WHEN sqBAQ.is_system_named = 1 THEN 'Yes'
    		ELSE ''
    		END) AS system_named
    	,(CASE
    		WHEN sqBAQ.is_primary_key = 1 THEN 'Yes'
    		ELSE ''
    		END) AS is_pk
    	,(CASE
    		WHEN sqBAQ.is_unique_constraint = 1 THEN 'C'
    		WHEN sqBAQ.is_unique = 1 THEN 'I'
    		ELSE ''
    		END) AS [unique]
    	,(CASE
    		WHEN sqBAQ.is_disabled = 1 THEN 'Yes'
    		ELSE ''
    		END) AS [disabled]
    	,(CASE
    		WHEN sqBAQ.is_hypothetical = 1 THEN 'Yes'
    		ELSE ''
    		END) AS hypothetical
    	,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, sqCC.total_columns), 1)), 4, 23)) AS total_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.[index_columns]), 1)), 4, 23))
    		END) AS [index_columns]
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.include_columns), 1)), 4, 23))
    		END) AS include_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.[index_columns] / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS index_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.include_columns / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS include_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, ((caMDKL.[index_columns] + caMDKL.include_columns) / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS total_pct_of_columns
    	,CONVERT (VARCHAR (25), CONVERT (MONEY, (ISNULL (sqBAQ.individual_index_pages, 0) * 8) / 1024.0), 1) AS key_mb
    	,CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.individual_index_pages + .0) / sqBAQ.index_pages) * 100, 0))) AS key_mb_pct
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqKL.total_max_length), 1)), 4, 22))
    		END) AS max_key_size_bytes
    	,(CASE
    		WHEN sqKL.total_max_length > 900 THEN 'Yes'
    		ELSE ''
    		END) AS over_key_size_limit
    	,ISNULL (ttIBKF.index_key, N'') AS index_key
    	,ISNULL (ttIBKF.include_key, N'') AS include_key
    	,ISNULL (ttIBKF.filter_definition, N'') AS filter_definition
    	,(CASE
    		WHEN sqED02.dupe_id IS NOT NULL THEN CONVERT (VARCHAR (20), sqED02.dupe_id) + ' - [' + CONVERT (VARCHAR (11), sqED02.total_dupes) + ']'
    		ELSE ''
    		END) AS dupe_id
    	,sqBAQ.is_unused AS unused
    	,ISNULL (CONVERT (VARCHAR (10), STATS_DATE (sqBAQ.[object_id], sqBAQ.index_id), 23), '') AS statistics_date
    	,(CASE
    		WHEN sqBAQ.[allow_row_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_row_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS row_locks
    	,(CASE
    		WHEN sqBAQ.[allow_page_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_page_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS page_locks
    	,(CASE
    		WHEN sqBAQ.[ignore_dup_key] = 0 THEN 'No'
    		WHEN sqBAQ.[ignore_dup_key] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS ignore_dupes
    	,(CASE
    		WHEN sqBAQ.no_recompute = 0 THEN 'Yes'
    		WHEN sqBAQ.no_recompute = 1 THEN 'No'
    		ELSE ''
    		END) AS auto_stats
    	,(CASE
    		WHEN sqBAQ.is_padded = 0 THEN 'No'
    		WHEN sqBAQ.is_padded = 1 THEN 'Yes'
    		ELSE ''
    		END) AS padded
    	,(CASE
    		WHEN sqBAQ.fill_factor = 0 THEN 100
    		ELSE sqBAQ.fill_factor
    		END) AS fill_factor
    	,(CASE
    		WHEN sqBAQ.user_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_seeks)
    		ELSE ''
    		END) AS user_seeks
    	,(CASE
    		WHEN sqBAQ.user_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_scans)
    		ELSE ''
    		END) AS user_scans
    	,(CASE
    		WHEN sqBAQ.user_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_lookups)
    		ELSE ''
    		END) AS user_lookups
    	,(CASE
    		WHEN sqBAQ.user_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_updates)
    		ELSE ''
    		END) AS user_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_seek, 23), '') AS last_user_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_scan, 23), '') AS last_user_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_lookup, 23), '') AS last_user_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_update, 23), '') AS last_user_update
    	,(CASE
    		WHEN sqBAQ.system_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_seeks)
    		ELSE ''
    		END) AS system_seeks
    	,(CASE
    		WHEN sqBAQ.system_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_scans)
    		ELSE ''
    		END) AS system_scans
    	,(CASE
    		WHEN sqBAQ.system_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_lookups)
    		ELSE ''
    		END) AS system_lookups
    	,(CASE
    		WHEN sqBAQ.system_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_updates)
    		ELSE ''
    		END) AS system_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_seek, 23), '') AS last_system_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_scan, 23), '') AS last_system_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_lookup, 23), '') AS last_system_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_update, 23), '') AS last_system_update
    FROM
    
    	(
    		SELECT
    			 O.[type]
    			,O.[schema_id]
    			,O.[object_id]
    			,CONVERT (VARCHAR (10), O.create_date, 23) AS create_date
    			,CONVERT (VARCHAR (10), O.modify_date, 23) AS modify_date
    			,sqDDPS.[rows]
    			,sqDDPS.total_pages
    			,sqDDPS.used_pages
    			,(CASE
    				WHEN sqDDPS.total_pages > sqDDPS.used_pages THEN sqDDPS.total_pages - sqDDPS.used_pages
    				ELSE 0
    				END) AS unused_pages
    			,sqDDPS.data_pages
    			,(CASE
    				WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN sqDDPS.used_pages - sqDDPS.data_pages
    				ELSE 0
    				END) AS index_pages
    			,sqI.type_desc
    			,sqI.name AS index_name
    			,sqI.is_system_named
    			,sqI.is_primary_key
    			,sqI.is_unique
    			,sqI.is_disabled
    			,sqI.is_hypothetical
    			,sqI.individual_index_pages
    			,sqI.is_unused
    			,sqI.[allow_row_locks]
    			,sqI.[allow_page_locks]
    			,sqI.[ignore_dup_key]
    			,sqI.no_recompute
    			,sqI.is_padded
    			,sqI.fill_factor
    			,sqI.user_seeks
    			,sqI.user_scans
    			,sqI.user_lookups
    			,sqI.user_updates
    			,sqI.last_user_seek
    			,sqI.last_user_scan
    			,sqI.last_user_lookup
    			,sqI.last_user_update
    			,sqI.system_seeks
    			,sqI.system_scans
    			,sqI.system_lookups
    			,sqI.system_updates
    			,sqI.last_system_seek
    			,sqI.last_system_scan
    			,sqI.last_system_lookup
    			,sqI.last_system_update
    			,sqI.is_unique_constraint
    			,sqI.index_id
    			,sqI.row_filter
    		FROM
    			sys.objects O
    			INNER JOIN
    
    				(
    					SELECT
    						 ttIBSI.[object_id]
    						,SUM (ttIBSI.[rows]) AS [rows]
    						,SUM (ttIBSI.total_pages) AS total_pages
    						,SUM (ttIBSI.used_pages) AS used_pages
    						,SUM (ttIBSI.data_pages) AS data_pages
    					FROM
    						dbo.#temp_index_breakdown_size_info ttIBSI
    					GROUP BY
    						ttIBSI.[object_id]
    				) sqDDPS ON sqDDPS.[object_id] = O.[object_id]
    
    			INNER JOIN
    
    				(
    					SELECT
    						 I.[object_id]
    						,I.type_desc
    						,I.name
    						,KC.is_system_named
    						,I.is_primary_key
    						,I.is_unique
    						,I.is_disabled
    						,I.is_hypothetical
    						,(CASE
    							WHEN ttIBSI.used_pages > ttIBSI.data_pages THEN ttIBSI.used_pages - ttIBSI.data_pages
    							END) AS individual_index_pages
    						,(CASE
    							WHEN I.[type] = 0 THEN ''
    							WHEN I.[type] = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_primary_key = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_unique = 1 THEN REPLICATE ('.', 6)
    							WHEN EXISTS
    
    								(
    									SELECT
    										*
    									FROM
    										sys.index_columns IC
    										INNER JOIN sys.foreign_key_columns FKC ON FKC.parent_object_id = IC.[object_id]
    											AND FKC.parent_column_id = IC.column_id
    									WHERE
    										IC.[object_id] = I.[object_id]
    										AND IC.index_id = I.index_id
    								) THEN REPLICATE ('.', 6)
    
    							WHEN DDIUS.[object_id] IS NOT NULL THEN (CASE
    																		WHEN DDIUS.user_seeks + DDIUS.user_scans + DDIUS.user_lookups + DDIUS.user_updates = 0 THEN 'Y/N'
    																		ELSE 'No'
    																		END)
    							ELSE 'Yes'
    							END) AS is_unused
    						,I.[allow_row_locks]
    						,I.[allow_page_locks]
    						,I.[ignore_dup_key]
    						,S.no_recompute
    						,I.is_padded
    						,I.fill_factor
    						,DDIUS.user_seeks
    						,DDIUS.user_scans
    						,DDIUS.user_lookups
    						,DDIUS.user_updates
    						,DDIUS.last_user_seek
    						,DDIUS.last_user_scan
    						,DDIUS.last_user_lookup
    						,DDIUS.last_user_update
    						,DDIUS.system_seeks
    						,DDIUS.system_scans
    						,DDIUS.system_lookups
    						,DDIUS.system_updates
    						,DDIUS.last_system_seek
    						,DDIUS.last_system_scan
    						,DDIUS.last_system_lookup
    						,DDIUS.last_system_update
    						,I.is_unique_constraint
    						,I.index_id
    						,ROW_NUMBER () OVER
    											(
    												PARTITION BY
    													I.[object_id]
    												ORDER BY
    													 I.is_primary_key DESC
    													,(CASE
    														WHEN I.[type] = 0 THEN 'Z'
    														ELSE 'A'
    														END)
    													,I.[type]
    													,I.name
    											) AS row_filter
    					FROM
    						sys.indexes I
    						INNER JOIN dbo.#temp_index_breakdown_size_info ttIBSI ON ttIBSI.[object_id] = I.[object_id]
    							AND ttIBSI.index_id = I.index_id
    						LEFT JOIN sys.key_constraints KC ON KC.parent_object_id = I.[object_id]
    							AND KC.unique_index_id = I.index_id
    						LEFT JOIN sys.stats S ON S.[object_id] = I.[object_id]
    							AND S.stats_id = I.index_id
    						LEFT JOIN master.sys.dm_db_index_usage_stats DDIUS ON DDIUS.[object_id] = I.[object_id]
    							AND DDIUS.index_id = I.index_id
    							AND DDIUS.database_id = @Database_ID
    				) sqI ON sqI.[object_id] = O.[object_id]
    
    		WHERE
    			O.[type] IN ('U', 'V')
    			AND O.is_ms_shipped = 0
    			AND NOT
    
    				(
    					SCHEMA_NAME (O.[schema_id]) = N'dbo'
    					AND O.name = N'sysdiagrams'
    					AND O.[type] = 'U'
    				)
    
    	) sqBAQ
    
    	INNER JOIN
    
    		(
    			SELECT
    				 C.[object_id]
    				,COUNT (*) + .0 AS total_columns
    			FROM
    				sys.columns C
    			GROUP BY
    				C.[object_id]
    		) sqCC ON sqCC.[object_id] = sqBAQ.[object_id]
    
    	LEFT JOIN dbo.#temp_index_breakdown_keys_filters ttIBKF ON ttIBKF.[object_id] = sqBAQ.[object_id]
    		AND ttIBKF.index_id = sqBAQ.index_id
    	LEFT JOIN
    
    		(
    			SELECT
    				 IC.[object_id]
    				,IC.index_id
    				,SUM (C.max_length) AS total_max_length
    			FROM
    				sys.index_columns IC
    				INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    					AND C.column_id = IC.column_id
    			WHERE
    				IC.is_included_column = 0
    			GROUP BY
    				 IC.[object_id]
    				,IC.index_id
    		) sqKL ON sqKL.[object_id] = sqBAQ.[object_id] AND sqKL.index_id = sqBAQ.index_id
    
    	LEFT JOIN
    
    		(
    			SELECT
    				 sqED01.dupe_rank
    				,sqED01.total_dupes
    				,ROW_NUMBER () OVER
    									(
    										ORDER BY
    											(SELECT NULL)
    									) AS dupe_id
    			FROM
    
    				(
    					SELECT
    						 ttIBKF.dupe_rank
    						,COUNT (*) AS total_dupes
    					FROM
    						dbo.#temp_index_breakdown_keys_filters ttIBKF
    					GROUP BY
    						ttIBKF.dupe_rank
    					HAVING
    						COUNT (*) > 1
    				) sqED01
    
    		) sqED02 ON sqED02.dupe_rank = ttIBKF.dupe_rank
    
    	CROSS APPLY
    
    		(
    			SELECT
    				 (CASE
    					WHEN sqBAQ.modify_date = sqBAQ.create_date THEN REPLICATE ('.', 18)
    					ELSE sqBAQ.modify_date
    					END) AS modify_date
    				,LEN (ttIBKF.index_key) - LEN (REPLACE (ttIBKF.index_key, '•', '')) AS [index_columns]
    				,ISNULL (LEN (ttIBKF.include_key) - LEN (REPLACE (ttIBKF.include_key, '•', '')), 0) AS include_columns
    		) caMDKL
    
    ORDER BY
    	 sqBAQ.[type]
    	,SCHEMA_NAME (sqBAQ.[schema_id])
    	,OBJECT_NAME (sqBAQ.[object_id])
    	,sqBAQ.row_filter
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Cleanup: Drop Any Remaining Temp Tables
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END

    public by cghersi @ MailUp API DEV  112992  11  5  0

    How to Retrieve your personal information with MailUp .NET SDK

    This snippet takes advantage of the MailUp .NET SDK to setup the secure communication towards the server through OAuth2 two leg authentication. You can start digging into the MailUP API world by reading this content: http://help.mailup.com/display/mailupapi/REST+API An instance of MailUpClient class is able to manage one user at a time. A MailU
    public static void GetPersonalDataFields(string clientID, string clientSecret, string username, string password)
    {
        // 1) Setup the MailUp Client:
        MailUpClient client = new MailUpClient(clientID, clientSecret);
        try
        {
            client.RetrieveAccessToken(username, password);
        }
        catch (MailUpException ex)
        {
            Console.WriteLine("Unable to access the service due to " + ex.Message);
            return;
        }
    
        // 2) Prepare the input:
        //nothing to do here...
    
        // 3) Invoke the web method:
        CollectionOf<ConsoleDynamicFieldItem> result = null;
        try
        {
            result = client.GetPersonalDataFields();
        }
        catch (Exception ex)
        {
            Console.WriteLine("Cannot perform the operation due to " + ex.Message);
            return;
        }
    
    
        if (result != null)
            Console.WriteLine("Personal Data Fields Retrieved: " + result.Count);
        else
            Console.WriteLine("Cannot retrieve Personal Data Fields.");
    }
    

    public by Geometry  88724  0  6  0

    ToSampleFormat: Converts a <see cref="WaveFormatEncoding"/> and bits per sample information into an appropriate <see cref="SampleFormat"/>.

    Converts a and bits per sample information into an appropriate . The wave format tag. The bits per sample.
    /// <summary>
    ///   Converts a <see cref="WaveFormatEncoding"/> and bits per sample information
    ///   into an appropriate <see cref="SampleFormat"/>.
    /// </summary>
    /// 
    /// <param name="tag">The wave format tag.</param>
    /// <param name="bitsPerSample">The bits per sample.</param>
    /// 
    public static SampleFormat ToSampleFormat(this WaveFormatEncoding tag, int bitsPerSample)
    {
        if (tag == WaveFormatEncoding.Pcm)
        {
            if (bitsPerSample == 16)
                return SampleFormat.Format16Bit;
            else if (bitsPerSample == 32)
                return SampleFormat.Format32Bit;
        }
        else if (tag == WaveFormatEncoding.IeeeFloat)
        {
            if (bitsPerSample == 32)
                return SampleFormat.Format32BitIeeeFloat;
            else if (bitsPerSample == 64)
                return SampleFormat.Format64BitIeeeFloat;
        }
    
        throw new ArgumentOutOfRangeException("tag", "Unsupported format tag.");
    }

    public by sherazkhan  2094  1  7  0

    How to Reads Outlook PST File & Displays Folder or Message Information in Android Apps

    This technical tip shows how to get the messages information from the Outlook PST file and display it inside .NET Applications. Previously we have discussed how to load an Outlook PST file and browse through its folders to get folder names and the number of messages in each folder. This topic explains how to read all the folders and subfolders in
    public void RetrievePSTFolderContents()
        {
            String strBaseFolder = Environment.getExternalStorageDirectory().getPath() + "/";                
            String pstFileName = strBaseFolder + "Outlook.pst";
    
            try
            {
                // Load the Outlook PST file
                PersonalStorage pst = PersonalStorage.fromFile(pstFileName);
    
                // Get the root folder
                FolderInfo folderInfo = pst.getRootFolder();
    
                // Call the recursive method to display the folder contents
                DisplayFolderContents(folderInfo, pst);
            }
            catch (Exception ex)
            {
                tx.setText(ex.getMessage());
            }
        }
    
        private static void DisplayFolderContents(FolderInfo folderInfo, PersonalStorage pst)
        {        // Display the folder name
            tx.setText(tx.getText() + "\n Folder: " + folderInfo.getDisplayName());
            // Display information about messages inside this folder
            MessageInfoCollection messageInfoCollection = folderInfo.getContents();
            for (int i = 0; i < messageInfoCollection.size(); i++)
            {
                MessageInfo messageInfo = (MessageInfo) messageInfoCollection.get(i);
                tx.setText(tx.getText() + "\n Subject: " + messageInfo.getSubject());
                tx.setText(tx.getText() + "\n Sender: " + messageInfo.getSenderRepresentativeName());
                tx.setText(tx.getText() + "\n To: " + messageInfo.getDisplayTo());
                tx.setText(tx.getText() + "\n CC: " + messageInfo.getDisplayCC());
                tx.setText(tx.getText() + "\n EntryID: " + messageInfo.getEntryIdString());
            }
    
            // Call this method recursively for each subfolder
            if (folderInfo.hasSubFolders() == true)
            {
                for (int i = 0; i < folderInfo.getSubFolders().size(); i++)
                {
                    FolderInfo subfolderInfo = (FolderInfo) folderInfo.getSubFolders().get(i);
                    DisplayFolderContents(subfolderInfo, pst);
                }
            }
        }
              

    public by lbottaro @ Amazon_AWS_Python_API  6260  3  8  0

    Amazon S3 Aws - How to get content information for a given bucket using boto python api

    This snippet shows how to iterate through your own Amazon S3 buckets and list the available content.
    import boto
    import boto.s3.connection
    access_key = '<aws access key>'
    secret_key = '<aws secret key>'
    
    # This code initialize the Amazon S3 connection to your account
    conn = boto.connect_s3(
            aws_access_key_id = access_key,
            aws_secret_access_key = secret_key)
    
    # Iteration through all available buckets stored into S3
    # Name and creation date are listed
    for bucket in conn.get_all_buckets():
            print "{name}\t{created}".format(
                    name = bucket.name,
                    created = bucket.creation_date,
            )
            # For each bucket get all data content put into the bucket
            for key in bucket.list():
                    # Get name, size and date
                    print "{name}\t{size}\t{modified}".format(
                            name = key.name,
                            size = key.size,
                            modified = key.last_modified,
                            )

    public by cghersi  4834  6  7  1

    IntelliJ: How to programmatically retrieve information like language, OS, Architecture, etc. regarding the current project

    This is the fastest method to retrieve information regarding your project and current file, if you are developing an IntelliJ plugin.
    public void actionPerformed(AnActionEvent event) {
    	com.intellij.openapi.actionSystem.DataContext dataContext = event.getDataContext();
    	
    	//retrieve the current file selected in the editor:
    	com.intellij.openapi.vfs.VirtualFile currentFile = null;
    	if (dataContext != null) 
    		currentFile = DataKeys.VIRTUAL_FILE.getData(dataContext);
    
    	com.intellij.openapi.project.Project project = event.getData(PlatformDataKeys.PROJECT);
    	String jvmVersionName = "";
    	if (project != null) {
    		com.intellij.openapi.roots.ProjectRootManager rootMan = 
    			com.intellij.openapi.roots.ProjectRootManager.getInstance(project);
    		if (rootMan != null) {
    			com.intellij.openapi.projectRoots.Sdk projectSdk = rootMan.getProjectSdk();
                   if (projectSdk != null) {
    				String ver = projectSdk.getVersionString(); //this is something like "java version "jdk1.7.045""
    				if (ver != null) {
    					System.out.println("Found Version string:" + ver);
                       }
    			}
    		}
    	}
    }

    public by cghersi  3835  0  6  3

    How to clean all the data from the tables of a MS SQL Server database

    This procedure allows to clean all the data from all the tables of the given DB. Use @rese_ident = 1 if you want to restart the identity columns from the default value.
    CREATE PROCEDURE [dbo].[CleanDB]
    	@db_name sysname = 'MyDBName',
    	@reset_ident tinyint = 0
    as
    begin
    	set nocount on
    
    	if @db_name is null
    		set @db_name = db_name()
    
    	if @reset_ident is null
    		set @reset_ident = 0
    
    	if not exists(select * from sys.databases where name = @db_name and database_id > 4)
    	begin
    		raiserror('Database does not exist or it can not be cleared', 16, 1)
    		return
    	end
    
    	declare @recovery_mode sysname
    
    	select @recovery_mode = cast(DatabasePropertyEx(@db_name,'Recovery') as sysname)
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd nvarchar(max)
    
    		print '-- Changing database recovery mode to SIMPLE'
    
    		set @ncmd = 'alter database [' + @db_name + '] set recovery simple'
    
    		print @ncmd
    
    		exec sp_executesql @ncmd
    	end
    
    	-------------------------------------------------------------------------------------------
    	-- prepare table with tables list
    
    	create table #temp_tables
    	(
    		rec_id int identity(1, 1) primary key not null,
    		schema_name sysname not null,
    		table_name sysname not null
    	)
    
    	declare @n_cmd nvarchar(max)
    
    	set @n_cmd = 'insert into #temp_tables (schema_name, table_name) select ss.name as schema_name, st.name as table_name from [' + @db_name + '].sys.tables as st inner join [' + @db_name + '].sys.schemas as ss on ss.schema_id = st.schema_id where ss.name <> ''sys'' '
    
    	exec sp_executesql @n_cmd
    
    	-------------------------------------------------------------------------------------------
    	-- disable constraints
    
    	declare @table_name sysname
    	declare @schema_name sysname
    	declare @counter_max int
    	declare @counter int
    
    	select @counter_max = max(rec_id) from #temp_tables
    
    	if @counter_max is null
    		set @counter_max = 0
    
    	declare @object_name nvarchar(max)
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = N'alter table ' + @object_name + N' nocheck constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not disable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	----------------------------------------------------------------------------------------------
    	-- delete records from tables
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @object_name = N'[' + @db_name + N'].[' + @schema_name + N'].[' + @table_name + N']'
    
    		set @n_cmd = 'delete ' + @object_name
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    
    			if @reset_ident = 1
    			begin
    				set @n_cmd = 'if exists(select * from [' + @db_name + '].sys.columns where object_id = object_id(''' + 
    					@object_name + ''') and is_identity = 1) dbcc checkident(''' + @object_name + ''', reseed)'
    
    				print @n_cmd
    
    				exec sp_executesql @n_cmd
    			end
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not clean table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	-----------------------------------------------------------------------------------------------
    	-- enable constraints
    
    	set @counter = @counter_max
    	while @counter > 0
    	begin
    		set @table_name = null
    		set @schema_name = null
    
    		select @table_name = table_name, @schema_name = schema_name from #temp_tables where rec_id = @counter
    
    		if @table_name is null or @schema_name is null
    			break
    
    		set @n_cmd = 'alter table [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] with check check constraint all'
    
    		print @n_cmd
    
    		begin try
    			exec sp_executesql @n_cmd
    		end try
    		begin catch
    			print '-------------------------------------------------------------------------'
    			print 'ERROR - Could not enable constraints for table ' + @object_name
    			print error_message()
    			print '-------------------------------------------------------------------------'
    		end catch
    
    		set @counter = @counter - 1
    	end
    
    	drop table #temp_tables
    
    	---------------------------------------------------------------------------------------------------
    	-- restore database recovery mode
    
    	if @recovery_mode <> 'Simple'
    	begin
    		declare @ncmd2 nvarchar(max)
    
    		print '-- Restoring database recovery mode'
    
    		set @ncmd2 = 'alter database [' + @db_name + '] set recovery ' + @recovery_mode
    
    		print @ncmd2
    
    		exec sp_executesql @ncmd2
    	end
    
    end

    public by cghersi @ MailUp API DEV  1307  26  7  0

    MailUp REST API - How to subscribe to a particular list several contacts with some information like email, mobile number, etc.

    This snippet takes advantage of the MailUp .NET SDK to setup the secure communication towards the server through OAuth2 two leg authentication. You can start digging into the MailUP API world by reading this content: http://help.mailup.com/display/mailupapi/REST+API An instance of MailUpClient class is able to manage one user at a time. A MailU
    public static void SubcribeRecipients(string clientID, string clientSecret, string username, string password)
    {
        // 1) Setup the MailUp Client:
        MailUpClient client = new MailUpClient(clientID, clientSecret);
        try
        {
            client.RetrieveAccessToken(username, password);
        }
        catch (MailUpException ex)
        {
            Console.WriteLine("Unable to access the service due to " + ex.Message);
            return;
        }
    
        // 2) Prepare the input:
        int idList = 1;
        List<ConsoleRecipientItem> recipients = new List<ConsoleRecipientItem>();
        ConsoleRecipientItem recipient1 = new ConsoleRecipientItem()
        {
            Email = "test1@mailup.com",
            Name = "First New Recipient" + DateTime.Now.ToFileTimeUtc(),
            MobileNumber = "3331234567",
            MobilePrefix = "39",
            Fields = new List<ConsoleRecipientDynamicFieldItem>()
            {
                new ConsoleRecipientDynamicFieldItem() { Id = 1, Value = "Name1_" + DateTime.Now.ToFileTimeUtc() },
                new ConsoleRecipientDynamicFieldItem() { Id = 2, Value = "LastName1_" + DateTime.Now.ToFileTimeUtc() }
                //here you can add all the fields you want to add...
            }
        };
        ConsoleRecipientItem recipient2 = new ConsoleRecipientItem()
        {
            Email = "test2@mailup.com",
            Name = "Second New Recipient" + DateTime.Now.ToFileTimeUtc(),
            MobileNumber = "3337654321",
            MobilePrefix = "39",
            Fields = new List<ConsoleRecipientDynamicFieldItem>()
            {
                new ConsoleRecipientDynamicFieldItem() { Id = 1, Value = "Name2_" + DateTime.Now.ToFileTimeUtc() },
                new ConsoleRecipientDynamicFieldItem() { Id = 2, Value = "LastName2_" + DateTime.Now.ToFileTimeUtc() }
                //here you can add all the fields you want to add...
            }
        };
        recipients.Add(recipient1);
        recipients.Add(recipient2);
    
        // 3) Invoke the web method:
        int result = 0;
        try
        {
            result = client.AddRecipientsToList(idList, recipients);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Cannot perform the operation due to " + ex.Message);
            return;
        }
    
        // 4) Print results:
        if (result > 0)
        {
            Console.WriteLine("Recipients Import started; import ID:" + result);
    
            //optionally we can check the import status:
            bool completed = false;
            do
            {
                ConsoleImportStatus importStatus = client.CheckImportStatus(result);
                if (importStatus == null)
                {
                    Console.WriteLine("Cannot check the import status for import task #" + result);
                    return;
                }
                completed = importStatus.Completed;
            } while (!completed);
            Console.WriteLine("Recipients Added!");
        }
        else
            Console.WriteLine("An error occurred while adding the recipients.");
    }
    

    public by cghersi  3078  5  6  0

    How to modify a PLAT.ONE user

    This snippets briefly shows how to modify the information of a user. You can change everything but userID and username.
    import com.abodata.plat1.ws.velocity.IProxyPlatUser;
    
    public class UserManager {
    	
    	public static void changeUserEmail(String email, int userID) throws Exception {
    		IProxyPlatUser user = HelloWorldServiceImpl.getAPI().DB_GetPlatUser(userID);
    		user.setEmail(email);
    		HelloWorldServiceImpl.getSUAPI().DB_ModifyPlatUser(user);
    	}
    }
    
    
    // This goes into your OSGI-INF/component.xml
    	<reference
    	   	interface="com.abodata.plat1.velocity.client.su.IPlatoneSUAPI"
    	   	cardinality="1..n"
    	   	policy="dynamic"
    	   	bind="setSUAPI"
    	   	unbind="removeSUAPI"/> 
    	<reference
    	   	interface="com.abodata.plat1.velocity.client.IPlatoneAPI"
    	   	cardinality="1..n"
    	   	policy="dynamic"
    	   	bind="setAPI"
    	   	unbind="removeAPI"/> 
    	   	
    // This is the fragment in your activator class:
    	static AtomicReference<IPlatoneSUAPI> suapiMan = new AtomicReference<IPlatoneSUAPI>();
    	
    	void setSUAPI(IPlatoneSUAPI arg) {
    		DSUtils.setRef(log, suapiMan, arg, IPlatoneSUAPI.class, this.getClass());
    	}
    	
    	void removeSUAPI(IPlatoneSUAPI arg) {
    		DSUtils.removeRef(log, suapiMan, arg, IPlatoneSUAPI.class, this.getClass());
    	}
    	
    	static IPlatoneSUAPI getSUAPI() {
    		return DSUtils.get(log, suapiMan, true);
    	}
    	
    	static AtomicReference<IPlatoneAPI> apiMan = new AtomicReference<IPlatoneAPI>();
    	
    	void setAPI(IPlatoneAPI arg) {
    		DSUtils.setRef(log, apiMan, arg, IPlatoneAPI.class, this.getClass());
    	}
    	
    	void removeAPI(IPlatoneAPI arg) {
    		DSUtils.removeRef(log, apiMan, arg, IPlatoneAPI.class, this.getClass());
    	}
    	
    	static IPlatoneAPI getAPI() {
    		return DSUtils.get(log, apiMan, true);
    	}          

    public by Geometry  1520  0  4  0

    SetActualRequestType: Specifies the actual type of <see cref="System.Net.Http.ObjectContent{T}"/> passed to the <see cref="System.Net.Http.HttpRequestMessage"/> in an action. The help page will use this information to produce more accurate re...

    Specifies the actual type of passed to the in an action. The help page will use this information to produce more accurate request samples. The . The type. Name of the controller. Name of the action. The parameter names.
    using System;
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.ComponentModel;
    using System.Diagnostics;
    using System.Diagnostics.CodeAnalysis;
    using System.Globalization;
    using System.Linq;
    using System.Net.Http;
    using System.Net.Http.Headers;
    using System.Web.Http;
    using System.Web.Http.Controllers;
    using System.Web.Http.Description;
    using PowerBIRestDemo.Areas.HelpPage.ModelDescriptions;
    using PowerBIRestDemo.Areas.HelpPage.Models;
    
    /// <summary>
    /// Specifies the actual type of <see cref="System.Net.Http.ObjectContent{T}"/> passed to the <see cref="System.Net.Http.HttpRequestMessage"/> in an action.
    /// The help page will use this information to produce more accurate request samples.
    /// </summary>
    /// <param name="config">The <see cref="HttpConfiguration"/>.</param>
    /// <param name="type">The type.</param>
    /// <param name="controllerName">Name of the controller.</param>
    /// <param name="actionName">Name of the action.</param>
    /// <param name="parameterNames">The parameter names.</param>
    public static void SetActualRequestType(this HttpConfiguration config, Type type, string controllerName, string actionName, params string[] parameterNames)
    {
        config.GetHelpPageSampleGenerator().ActualHttpMessageTypes.Add(new HelpPageSampleKey(SampleDirection.Request, controllerName, actionName, parameterNames), type);
    }
    • Public Snippets
    • Channels Snippets