working on it ...

Filters

Explore Public Snippets

Sort by

Found 17k snippets matching: error

    public by cghersi  328008  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 @ Thotpot alumni  303287  0  5  0

    Java: generate hprof during runtime

    To generate hprof may be interesting for debug purpose, most of all before an OutOfMemory occurs on production applications! This .ini option halps to create an hprof to be analyzed before the occurrence of memory errors. See http://docs.oracle.com/javase/7/docs/technotes/samples/hprof.html for further details
    java -Xrunhprof:heap=sites,depth=10,thread=y,doe=y ...other jvm options in ini file...

    public by DanielOgbuagu  201738  1  5  0

    Adding a *.jar file (originating from java-wrapped C++ code) to an SBT project classpath

    I thought using unmanaged dependencies in SBT projects was simply dropping a jar file into the lib directory, until I bought an SDK for a biometric device. Both the C++ and the Java packages were provided. I opted for the Java package, copying the provided jar file and the JNI (*.so) files into the appropriate locations and configuring the ja
    /**
     * In my <code>build.sbt</code> file, I appended a blank line and then the following:
     */
    
    libraryDependencies += "groupID" % "artifactId" % "revision" from "file:///home/Danogbuagu/Projects/biometric-lib/filename.jar"
    
    // where,
    // groupID for example is like: com.zigzagcompany 
    // artifactId for example is like: zigzag
    // revision for example is like: 1.3.4
    // Remember the line befor the code and that after.

    public by augustogiles  100047  0  3  0

    Error rake db:schema:load

    Error rake db:schema:load: gistfile1.txt
    rake db:schema:load
    rake aborted!
    /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/trace_output.rb:18:in `block in trace_on': invalid byte sequence in US-ASCII (ArgumentError)
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/trace_output.rb:16:in `map'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/trace_output.rb:16:in `trace_on'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/application.rb:328:in `trace'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/application.rb:183:in `display_error_message'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/application.rb:169:in `rescue in standard_exception_handling'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/application.rb:159:in `standard_exception_handling'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/lib/rake/application.rb:70:in `run'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/gems/rake-10.0.4/bin/rake:33:in `<top (required)>'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/bin/rake:23:in `load'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/bin/rake:23:in `<main>'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/bin/ruby_executable_hooks:15:in `eval'
            from /usr/local/rvm/gems/ruby-1.9.3-p551/bin/ruby_executable_hooks:15:in `<main>'
    
    
    

    public by sherazam  2763  0  5  0

    How to Add Fixed or Custom Error Bar Value for Chart inside .NET Applications

    // Enter here the actual content of the snippet.            // Adding Fixed Error Bar Value for Chart
    
    //[C# Code Sample]
     
    
    using (Presentation pres = new Presentation())
    {
        //Creating a bubble chart
        IChart chart = pres.Slides[0].Shapes.AddChart(ChartType.Bubble, 50, 50, 400, 300, true);
    
        //Adding Error bars and setting its format
        IErrorBarsFormat errBarX = chart.ChartData.Series[0].ErrorBarsXFormat;
        IErrorBarsFormat errBarY = chart.ChartData.Series[0].ErrorBarsYFormat;
        errBarX.IsVisible = true;
        errBarY.IsVisible = true;
        errBarX.ValueType = ErrorBarValueType.Fixed;
        errBarX.Value = 0.1f;
        errBarY.ValueType = ErrorBarValueType.Percentage;
        errBarY.Value = 5;
        errBarX.Type = ErrorBarType.Plus;
        errBarY.Format.Line.Width = 2;
        errBarX.HasEndCap = true;
     
        //Saving presentation
        pres.Save("ErrorBars.pptx", SaveFormat.Pptx);
    }
     
    //[VB.NET Code Sample]
     
    'Creating empty presentation
    Using pres As Presentation = New Presentation()
    
        'Creating a bubble chart
        Dim chart As IChart = pres.Slides(0).Shapes.AddChart(ChartType.Bubble, 50, 50, 400, 300, True)
    
        'Adding Error bars and setting its format
        Dim errBarX As IErrorBarsFormat = chart.ChartData.Series(0).ErrorBarsXFormat
        Dim errBarY As IErrorBarsFormat = chart.ChartData.Series(0).ErrorBarsYFormat
        errBarX.IsVisible = True
        errBarY.IsVisible = True
        errBarX.ValueType = ErrorBarValueType.Fixed
        errBarX.Value = 0.1F
        errBarY.ValueType = ErrorBarValueType.Percentage
        errBarY.Value = 5
        errBarX.Type = ErrorBarType.Plus
        errBarY.Format.Line.Width = 2
        errBarX.HasEndCap = True
    
        'Saving presentation
        pres.Save("ErrorBars.pptx", SaveFormat.Pptx)
    End Using
    
    // Adding Custom Error Bar Value for Chart
    
    //[C# Code Sample]
     
    //Aspose.Slides for .NET provides a simple API for managing custom error bar values.
    
    //Creating empty presentation
    using (Presentation pres = new Presentation())
    {
        //Creating a bubble chart
        IChart chart = pres.Slides[0].Shapes.AddChart(ChartType.Bubble, 50, 50, 400, 300, true);
    
        //Adding custom Error bars and setting its format
        IChartSeries series = chart.ChartData.Series[0];
        IErrorBarsFormat errBarX = series.ErrorBarsXFormat;
        IErrorBarsFormat errBarY = series.ErrorBarsYFormat;
        errBarX.IsVisible = true;
        errBarY.IsVisible = true;
        errBarX.ValueType = ErrorBarValueType.Custom;
        errBarY.ValueType = ErrorBarValueType.Custom;
       
        //Accessing chart series data point and setting error bars values for individual point
        IChartDataPointCollection points = series.DataPoints;
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForXPlusValues = DataSourceType.DoubleLiterals;
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForXMinusValues = DataSourceType.DoubleLiterals;
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForYPlusValues = DataSourceType.DoubleLiterals;
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForYMinusValues = DataSourceType.DoubleLiterals;
        
        //Setting error bars for chart series points
        for (int i = 0; i < points.Count; i++)
        {
            points[i].ErrorBarsCustomValues.XMinus.AsLiteralDouble = i + 1;
            points[i].ErrorBarsCustomValues.XPlus.AsLiteralDouble = i + 1;
            points[i].ErrorBarsCustomValues.YMinus.AsLiteralDouble = i + 1;
            points[i].ErrorBarsCustomValues.YPlus.AsLiteralDouble = i + 1;
        }
    
        //Saving presentation
        pres.Save("ErrorBarsCustomValues", SaveFormat.Pptx);
    }
     
    //[VB.NET Code Sample]
     
    'Creating empty presentation
    Using pres As Presentation = New Presentation()
    
        'Creating a bubble chart
        Dim chart As IChart = pres.Slides(0).Shapes.AddChart(ChartType.Bubble, 50, 50, 400, 300, True)
        Dim series As IChartSeries = chart.ChartData.Series(0)
    
        'Adding custom Error bars and setting its format
        Dim errBarX As IErrorBarsFormat = series.ErrorBarsXFormat
        Dim errBarY As IErrorBarsFormat = series.ErrorBarsYFormat
        errBarX.IsVisible = True
        errBarY.IsVisible = True
        errBarX.ValueType = ErrorBarValueType.Custom
        errBarY.ValueType = ErrorBarValueType.Custom
    
        'Accessing chart series data point and setting error bars values for individual point
    
        Dim points As IChartDataPointCollection = series.DataPoints
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForXPlusValues = DataSourceType.DoubleLiterals
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForXMinusValues = DataSourceType.DoubleLiterals
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForYPlusValues = DataSourceType.DoubleLiterals
        points.DataSourceTypeForErrorBarsCustomValues.DataSourceTypeForYMinusValues = DataSourceType.DoubleLiterals
    
        'Setting error bars for chart series points
        For i As Integer = 0 To points.Count
    
    
            points(i).ErrorBarsCustomValues.XMinus.AsLiteralDouble = i + 1
            points(i).ErrorBarsCustomValues.XPlus.AsLiteralDouble = i + 1
            points(i).ErrorBarsCustomValues.YMinus.AsLiteralDouble = i + 1
            points(i).ErrorBarsCustomValues.YPlus.AsLiteralDouble = i + 1
    
        Next
    
        'Saving presentation
        pres.Save("ErrorBarsCustomValues", SaveFormat.Pptx)
    
    End Using
    

    public by fabio.nosenzo  3584  1  6  0

    Manage errors excel VBA

    Command to be used as Iferror() formula
    ' example with vlookup formula
    
            sVlookup = Application.VLookup(Cells(i, j), Range("A1:C101"), 2, False)
            If IsError(sVlookup) Then sVlookup = "not found"
                            

    public by lbottaro  2923  0  7  1

    Stored Procedure to solve error about Agent XPs in SQL Server on Maintenance Plan

    This is how to solve the error in Microsoft SQL Server Management Studio, when trying to create a Maintenance Plan. The error you get is the following. Error Message: ‘Agent XPs’ component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For mo
    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Agent XPs', 1;
    GO
    RECONFIGURE
    GO            

    public by imagelesskink  4808  3  5  0

    Python code error

    Hi guys, I need help to figure out how to fix errors in my Python code Assignment. Basically I need create a program that should calculate the monthly average price for Google and tell us the best and worst six-month period for Google. The average price is defined as ((v1*c1)+(v2*c2)+(v3*c3)+(v4*c4)...+(vn*cn)) / (v1+v2+v3+v4...+vn) where vi i
    # I commented out the original code that was causing the errors and put comments by my fixes.
    def get_values():
        '''
            the "csv" file is a "comma-separated file", so we can split the data on commas.
            The following function will read a file, split the lines in the file on commas,
            and put the data in to a list that is returned. The result is a list of lists
            where each line
            is a list. Also, every item is a string.
        '''
    
    import operator
    
    data_file = open("googlePrices.csv")
    
    
    def get_data_list(file_name):
        data_list = []  # always start with an open list
        for line_str in data_file:
            data_list.append(line_str.strip().split(','))  # removes all commas from the csv file
        return data_list
    
    averageList = [] # variables that were assigned (with an '=') in a function are local to that function.
                     # even if you assigned them in a larger scope, whenever you use the assignment operator it creates a local copy.
    
    def monthlyAverage(list_of_tuples=[]):
        '''Calculates monthly average
           The average price is defined as
           ((v1*c1)+(v2*c2)+(v3*c3)+(v4*c4)...+(vn*cn)) / (v1+v2+v3+v4...+vn)
           where vi is the volume for day i and ci is the adjusted close price
           for day i.
        '''
    
        #averageList = []
        currentYear_int = 2012
        month_int = 11
        sum_float = float()
        count = 0
        date_year_str = "" # The assignment below is skipped if list_of_tuples is empty, so it needs to be set here so it at leasts exists if you try to use it.
    
        for a_tuple in list_of_tuples:
            dateStr = a_tuple[0]
            Floatdata = a_tuple[1]
            listdate = dateStr.split(',')
            Year_int = int(listdate[0])
            month_int = int(listdate[1])
            date_year_str = "Date: " + str(month_int) + "-" + str(currentYear_int)
    
        if month_int != currentYear_int:
            float_average = sum_float / count
            list_average = [date_year_str , float_average]
            average_tuple = tuple(list_average)
            averageList.append(average_tuple) # this is fine, since methods called on objects in higher scopes operate on that object, instead of making a copy.
            current_month_int = month_int
            sum_float = 0.0
            count = 0
            sum_float += Floatdata
            count += 1
    
        #return averageList
    #the return was no longer needed, since averageList is a global variable
    
    #def best_6_month(averageList):
    def best_6_month(): # If you define a function with a parameter, it needs to be called with the same number of parameters, unless they are given default values.
        averageList.sort()
        print("The 6 best months fo google are: ")
        print()
        print(averageList)
    
    
    #def worst_6_month(averageList):
    def worst_6_month():
        averageList.reverse()
        print("The 6 worst months for google are: ")
        print()
        print(averageList)
    
    
    optionStr = ""
    
    if optionStr != "0":
        print("------------------------------")
        print("\n Google Stock Prices. 2004 - 2012")
        print("------------------------------")
        print("Please choose an option")
        print()
        print("1.    Calculate Average")
        print("2.    View best six months")
        print("3.    View worst six months")
        print("0.    Exit program")
        print()
    
    optionStr = input("Enter you choice now: ")
    if (len(optionStr) != 1) or (optionStr not in "0123"):
        print("You have entered an invalid number!")
        print("Please try again")
    
    elif optionStr == "1":
        monthlyAverage()
    
    elif optionStr == "2":
        best_6_month()
    
    elif optionStr == "3":
        worst_6_month()
    
    elif optionStr == "0":
        print("\n Exiting program... Goodbye.")
    
    else:
        print("\n Bad option")

    public by mprisznyak  3056  4  4  1

    Error in VIM "Not an Editor Command"

    Not sure if I'm in the right place. I'm new to VIM and trying to set up _vimrc file. Copied the below portion of code exactly right out of a book on setting up this file. Does anybody see what the error is? I've been looking for two days! "set the status line options. Make it show more info. set laststatus=2 set statusline=%F%m%r%h%w\ [FORMAT=
    " mind escaping the space characters
    set laststatus=2
    set statusline=%F%m%r%h%w\ [FORMAT=%{&ff}]\ [TYPE=%Y]\ [POS=%l,%v][%p%%]\ %{strftime(\"%d/%m/%y\ -\ %H:%M\")}

    public by JKCPR  2853  4  6  1

    CUSTOM ERROR PAGE || HTTP STATUS REDIRECT

    Very useful for you to point all your error pages to the same file
    <?php
    // Very useful if you point all your error pages to the same file.
    
    // File; .htaccess
    /*# .htaccess file.
    
    ErrorDocument 404 /error-msg.php
    ErrorDocument 500 /error-msg.php
    ErrorDocument 400 /error-msg.php
    ErrorDocument 401 /error-msg.php
    ErrorDocument 403 /error-msg.php
    # End of file.
    
    // File; error-msg.php 
    */#
    ?>
    <?php
      $HttpStatus = $_SERVER["REDIRECT_STATUS"] ;
      if($HttpStatus==200) {print "Document has been processed and sent to you.";}
      if($HttpStatus==400) {print "Bad HTTP request ";}
      if($HttpStatus==401) {print "Unauthorized - Iinvalid password";}
      if($HttpStatus==403) {print "Forbidden";}
      if($HttpStatus==500) {print "Internal Server Error";}
      if($HttpStatus==418) {print "I'm a teapot! - This is a real value, defined in 1998";}
    
    ?>
    • Public Snippets
    • Channels Snippets