working on it ...

Filters

Explore Public Snippets

Sort by

Found 12k snippets matching: field

    public by cghersi modified Apr 11, 2014  327659  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 sherazam modified Jan 13, 2016  228084  0  5  0

    How to Insert Check Boxes or HTML into Word Docs during Mail Merge inside .NET Apps

    How to Insert Check Boxes or HTML into Word Docs during Mail Merge inside .NET Apps
    // Enter here the actual content of the snippet.            
    //[C# Code Sample]
     
    using System;
    using System.IO;
    using System.Reflection;
    using Aspose.Words;
    using Aspose.Words.Fields;
    using Aspose.Words.MailMerging;
    
    namespace MailMergeFormFields
    {
        /// <summary>
        /// This sample shows how to insert check boxes and text input form fields during mail merge into a document.
        /// </summary>
        class Program
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            public static void Main(string[] args)
            {
                Program program = new Program();
                program.Execute();
            }
    
            private void Execute()
            {
                string exeDir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + Path.DirectorySeparatorChar;
                string dataDir = new Uri(new Uri(exeDir), @"../../Data/").LocalPath;
    
                // Load the template document.
                Document doc = new Document(dataDir + "Template.doc");
    
                // Setup mail merge event handler to do the custom work.
                doc.MailMerge.FieldMergingCallback = new HandleMergeField();
    
                // This is the data for mail merge.
                String[] fieldNames = new String[] {"RecipientName", "SenderName", "FaxNumber", "PhoneNumber",
                    "Subject", "Body", "Urgent", "ForReview", "PleaseComment"};
                Object[] fieldValues = new Object[] {"Josh", "Jenny", "123456789", "", "Hello",
                    "<b>HTML Body Test message 1</b>", true, false, true};
    
                // Execute the mail merge.
                doc.MailMerge.Execute(fieldNames, fieldValues);
    
                // Save the finished document.
                doc.Save(dataDir + "Template Out.doc");
            }
    
            private class HandleMergeField : IFieldMergingCallback
            {
                /// <summary>
                /// This handler is called for every mail merge field found in the document,
                ///  for every record found in the data source.
                /// </summary>
                void IFieldMergingCallback.FieldMerging(FieldMergingArgs e)
                {
                    if (mBuilder == null)
                        mBuilder = new DocumentBuilder(e.Document);
    
                    // We decided that we want all boolean values to be output as check box form fields.
                    if (e.FieldValue is bool)
                    {
                        // Move the "cursor" to the current merge field.
                        mBuilder.MoveToMergeField(e.FieldName);
    
                        // It is nice to give names to check boxes. Lets generate a name such as MyField21 or so.
                        string checkBoxName = string.Format("{0}{1}", e.FieldName, e.RecordIndex);
    
                        // Insert a check box.
                        mBuilder.InsertCheckBox(checkBoxName, (bool)e.FieldValue, 0);
    
                        // Nothing else to do for this field.
                        return;
                    }
    
                    // We want to insert html during mail merge.
                    if (e.FieldName == "Body")
                    {
                        mBuilder.MoveToMergeField(e.FieldName);
                        mBuilder.InsertHtml((string)e.FieldValue);
                    }
    
                    // Another example, we want the Subject field to come out as text input form field.
                    if (e.FieldName == "Subject")
                    {
                        mBuilder.MoveToMergeField(e.FieldName);
                        string textInputName = string.Format("{0}{1}", e.FieldName, e.RecordIndex);
                        mBuilder.InsertTextInput(textInputName, TextFormFieldType.Regular, "", (string)e.FieldValue, 0);
                    }
                }
    
                void IFieldMergingCallback.ImageFieldMerging(ImageFieldMergingArgs args)
                {
                    // Do nothing.
                }
    
                private DocumentBuilder mBuilder;
            }
        }
    }
     
    //[VB.NET Code Sample]
     
    
    Imports Microsoft.VisualBasic
    Imports System
    Imports System.IO
    Imports System.Reflection
    Imports Aspose.Words
    Imports Aspose.Words.Fields
    Imports Aspose.Words.MailMerging
    
    Namespace MailMergeFormFields
        ''' <summary>
        ''' This sample shows how to insert check boxes and text input form fields during mail merge into a document.
        ''' </summary>
        Friend Class Program
            ''' <summary>
            ''' The main entry point for the application.
            ''' </summary>
            Public Shared Sub Main(ByVal args() As String)
                Dim program As New Program()
                program.Execute()
            End Sub
    
            Private Sub Execute()
                Dim exeDir As String = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + Path.DirectorySeparatorChar
                Dim dataDir As String = New Uri(New Uri(exeDir), "../../Data/").LocalPath
    
                ' Load the template document.
                Dim doc As New Document(dataDir & "Template.doc")
    
                ' Setup mail merge event handler to do the custom work.
                doc.MailMerge.FieldMergingCallback = New HandleMergeField()
    
                ' This is the data for mail merge.
                Dim fieldNames() As String = {"RecipientName", "SenderName", "FaxNumber", "PhoneNumber", "Subject", "Body", "Urgent", "ForReview", "PleaseComment"}
                Dim fieldValues() As Object = {"Josh", "Jenny", "123456789", "", "Hello", "<b>HTML Body Test message 1</b>", True, False, True}
    
                ' Execute the mail merge.
                doc.MailMerge.Execute(fieldNames, fieldValues)
    
                ' Save the finished document.
                doc.Save(dataDir & "Template Out.doc")
            End Sub
    
            Private Class HandleMergeField
                Implements IFieldMergingCallback
                ''' <summary>
                ''' This handler is called for every mail merge field found in the document,
                '''  for every record found in the data source.
                ''' </summary>
                Private Sub IFieldMergingCallback_FieldMerging(ByVal e As FieldMergingArgs) Implements IFieldMergingCallback.FieldMerging
                    If mBuilder Is Nothing Then
                        mBuilder = New DocumentBuilder(e.Document)
                    End If
    
                    ' We decided that we want all boolean values to be output as check box form fields.
                    If TypeOf e.FieldValue Is Boolean Then
                        ' Move the "cursor" to the current merge field.
                        mBuilder.MoveToMergeField(e.FieldName)
    
                        ' It is nice to give names to check boxes. Lets generate a name such as MyField21 or so.
                        Dim checkBoxName As String = String.Format("{0}{1}", e.FieldName, e.RecordIndex)
    
                        ' Insert a check box.
                        mBuilder.InsertCheckBox(checkBoxName, CBool(e.FieldValue), 0)
    
                        ' Nothing else to do for this field.
                        Return
                    End If
    
                    ' We want to insert html during mail merge.
                    If e.FieldName = "Body" Then
                        mBuilder.MoveToMergeField(e.FieldName)
                        mBuilder.InsertHtml(DirectCast(e.FieldValue, String))
                    End If
    
                    ' Another example, we want the Subject field to come out as text input form field.
                    If e.FieldName = "Subject" Then
                        mBuilder.MoveToMergeField(e.FieldName)
                        Dim textInputName As String = String.Format("{0}{1}", e.FieldName, e.RecordIndex)
                        mBuilder.InsertTextInput(textInputName, TextFormFieldType.Regular, "", CStr(e.FieldValue), 0)
                    End If
                End Sub
    
                Private Sub ImageFieldMerging(ByVal args As ImageFieldMergingArgs) Implements IFieldMergingCallback.ImageFieldMerging
                    ' Do nothing.
                End Sub
    
                Private mBuilder As DocumentBuilder
            End Class
        End Class
    End Namespace
    
    

    public by pan modified Oct 14, 2014  245241  2  5  0

    Replace all occurrences of a string in DB

    Searches and replaces all of occurrences of a string in all tables and all fields of the given Database
    <?php
    /**
    * @kudos http://stackoverflow.com/a/10182498
    */
     
    header("Content-Type: text/plain");
    
    $host = "localhost";
    $username = "root";
    $password = "";
    $database = "mydatabase";
    $string_to_replace  = 'old.example.com';
    $new_string = 'new.example.com';
    
    // Connect to database server
    mysql_connect($host, $username, $password);
    
    // Select database
    mysql_select_db($database);
    
    // List all tables in database
    $sql = "SHOW TABLES FROM ".$database;
    $tables_result = mysql_query($sql);
    
    if (!$tables_result) {
        echo "Database error, could not list tables\nMySQL error: " . mysql_error();
        exit;
    }
    
    echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
    while ($table = mysql_fetch_row($tables_result)) {
        echo "Table: {$table[0]}\n";
        $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
        if (!$fields_result) {
            echo 'Could not run query: ' . mysql_error();
            exit;
        }
        if (mysql_num_rows($fields_result) > 0) {
            while ($field = mysql_fetch_assoc($fields_result)) {
                if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
                    echo "  ".$field['Field']."\n";
                    $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
                    mysql_query($sql);
                }
            }
            echo "\n";
        }
    }
    
    mysql_free_result($tables_result);

    public by Geometry modified Aug 7, 2016  88496  0  5  0

    FromText: Creates a new CsvReader to read from a string.

    Creates a new CsvReader to read from a string. The text containing the fields in the CSV format. if field names are located on the first non commented line, otherwise, .
    /// <summary>
    ///   Creates a new CsvReader to read from a string.
    /// </summary>
    /// 
    /// <param name="text">The text containing the fields in the CSV format.</param>
    /// <param name="hasHeaders"><see langword="true"/> if field names are located on the first non commented line, otherwise, <see langword="false"/>.</param>
    /// 
    public static CsvReader FromText(string text, bool hasHeaders)
    {
        return new CsvReader(new StringReader(text), hasHeaders, text.Length);
    }

    public by kbudylov modified Jan 26, 2017  1746  14  5  0

    Yii2 $form->field

    <?= $form->field($model,'field',[
      //'template' => '{input}',
      //'options' => [
        //'class' => ''
      //]
    ])->textField([
      //'class' => ''
    ]) ?>

    public by arkanoid modified Mar 27, 2014  17858  0  6  2

    HTML - Removing the outline on an input field

    The annoying blue border outlining the input field in focus can be removed with a simple CSS rule
    input[type=text]:focus{
        outline: 0;
    }
    

    public by Geometry modified Jul 31, 2016  983  0  4  0

    BigIntFromBitsHex: Used to convert getblocktemplate bits field into target if target is not included.

    Used to convert getblocktemplate bits field into target if target is not included. / More info: https://en.bitcoin.it/wiki/Target
    using System;
    using System.IO;
    using System.Linq;
    using CoiniumServ.Coin.Address;
    using CoiniumServ.Coin.Address.Exceptions;
    using CoiniumServ.Cryptology;
    using CoiniumServ.Utils.Extensions;
    using CoiniumServ.Utils.Numerics;
    using Gibbed.IO;
    using Serilog;
    
    /// <summary>
    /// Used to convert getblocktemplate bits field into target if target is not included.
    //// More info: https://en.bitcoin.it/wiki/Target
    /// </summary>
    /// <param name="bits"></param>
    /// <returns></returns>
    public static BigInteger BigIntFromBitsHex(this string bits)
    {
        // TODO: implement a test for it!
    
        return bits.HexToByteArray().BigIntFromBitsBuffer();
    }

    public by cghersi modified Apr 24, 2014  1899  0  6  2

    Change XML field or element in SQL Server

    UPDATE	dbo.MyTable
    SET		  MyXMLColumn.modify('replace value of (path/to/element/text())[1] with "newvalue"')
    WHERE   pk = xxx

    public by sercani modified Jul 23, 2014  3538  1  7  4

    Reading Excel file and inserting into List with Serialization

    This class reads a sheet from excel file and insert into a list by matching the column names at sheet with member names of the class.
    using System.Data;
    using System.Data.Odbc;
    using System.Reflection;
    using System.Runtime.Remoting;
    using System;
    using System.Collections.Generic;
    
    public class ExcelReader
    {
        public List<DataItem> DataList = new List<DataItem>();
        public List<AnotherDataItem> AnotherDataList = new List<AnotherDataItem>();
        string fileName;
        string con;
        public ExcelReader()
        {
            fileName = "a.xls";
            con = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + fileName + ";";
            ReadAndFill<DataItem>("DataSheet", DataList);
            ReadAndFill<AnotherDataItem>("AnotherDataSheet", AnotherDataList);
        }
    
        void ReadAndFill<T>(string sheetName, List<T> list)
        {
            DataTable table = this.FillDataTable(sheetName);
            this.FillDataList<T>(table, list);
            table.Clear();
        }
    
        DataTable FillDataTable(string sheetName)
        {
            string query = "SELECT * FROM [" + sheetName + "$]";
            OdbcConnection oCon = new OdbcConnection(con);
            OdbcCommand oCmd = new OdbcCommand(query, oCon);
            DataTable dataTable = new DataTable(sheetName);
            oCon.Open();
            try
            {
                OdbcDataReader rData = oCmd.ExecuteReader();
                dataTable.Load(rData);
                rData.Close();
                oCon.Close();
            }
            catch ( Exception )
            {
                oCon.Close();
            }
            return dataTable;
        }
    
    
        void FillDataList<T>(DataTable table, List<T> list)
        {
            list.Clear();
            if ( table.Rows.Count <= 0 )
                return;
            FieldInfo [] fields = typeof(T).GetFields();
            foreach ( DataRow row in table.Rows )
            {
                if ( row.IsNull(0) )
                {
                    //"Sheet " + table.TableName + " has a null column at index = " + index);
                    continue;
                }
                object o = Activator.CreateInstance(typeof(T));
                foreach ( FieldInfo field in fields )
                {
                    try
                    {
                        if ( row.Field<object>(field.Name) == null )
                            continue;
                        if ( field.FieldType.IsEnum )
                            field.SetValue(o, Enum.Parse(field.FieldType, Convert.ToString(row.Field<object>(field.Name)), true));
                        else
                            field.SetValue(o, Convert.ChangeType(row.Field<object>(field.Name), field.FieldType));
                    }
                    catch ( Exception e )
                    {
                        if ( e.GetType() == typeof(IndexOutOfRangeException) )
                        {
                            //There is no column named as " + field.Name + " at sheet " + table.TableName;
                        }
                        else
                        {
                            //Excel Reading Error at sheet " + table.TableName + " at index = " + index + " at field = " + field.Name + "\nException Message = " + e.Message;
                        }
                    }
                }
                list.Add(( T )o);
            }
        }
    }
    
    

    public by bertelsen.kyle modified Oct 3, 2014  3096  1  5  0

    field base sample array

    Drupal 7 programmatically-added field base array
    return array(
    'widget_height' => array(
    	'active' => 1,
    	'cardinality' => 1,
    	'deleted' => 0,
    	'entity_types' => array(),
    	'field_name' => 'widget_height',
    	'foreign keys' => array(),
    	'indexes' => array(),
    	'label' => $t('Widget Height'),
    	'locked' => 0,
    	'module' => 'number',
    	'settings' => array(),
    	'translatable' => 0,
    	'type' => 'number_integer',
    ),
    );
    • Public Snippets
    • Channels Snippets