working on it ...

Filters

Explore Public Snippets

Sort by

Found 5,686 snippets matching: db

    public by przodownikR1 modified Jul 21, 2015  394760  1  3  0

    example create datasource in javaConfig with full desc and params

    example create datasource in javaConfig with full desc and params: db_config_full.java
    @Bean
      public DataSource dataSource() {
        final DataSource dataSource = new DataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(env.getProperty("jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.username"));
        dataSource.setPassword(env.getProperty("jdbc.password"));
        dataSource.setTestOnBorrow(true);
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnReturn(false);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setValidationInterval(30000);
        dataSource.setTimeBetweenEvictionRunsMillis(5000);
        dataSource.setMaxActive(100);
        dataSource.setMinIdle(10);
        dataSource.setMaxWait(10000);
        dataSource.setInitialSize(10);
        dataSource.setRemoveAbandonedTimeout(3600);
        dataSource.setRemoveAbandoned(true);
        dataSource.setLogAbandoned(true);
        dataSource.setMinEvictableIdleTimeMillis(30000);
        return dataSource;
      }
    
    

    public by cghersi modified Apr 11, 2014  327924  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 RSrini modified Jun 17, 2015  237469  43  3  0

    Installing db-sample-schema in windows (https://github.com/oracle/db-sample-schemas)

    Installing db-sample-schema in windows (https://github.com/oracle/db-sample-schemas): db-sample-schemas
    https://github.com/oracle/db-sample-schemas
    
    download the zip file and extract to "D:\app\rsrini7\product\12.1.0\dbhome_1\demo\schema"
    
    here rsrini7 is my username. extract the zip file to the dir according to your installation.
    
    open command prompt as administrator
    d: enter
    cd D:\app\rsrini7\product\12.1.0\dbhome_1\demo\schema
    
    mkdir log
    
    perl -p -i.bak -e 's#__SUB__CWD__#'D:/app/rsrini7/product/12.1.0/dbhome_1/demo/schema/'#g' *.sql */*.sql */*.dat
    
    (to install perl and other unix related tools - install babun - rocking tool for windows users. i love this )
    
    edit mksample.sql and all '_main.sql' files and follow the below instructions
    
    this is required : http://www.dba-oracle.com/t_ora_65096_create_user_12c_without_c_prefix.htm
    
    1. in connect statement 
    	add at the end 'as sysdba'
    2. after the connect statement add the below line
    	alter session set "_oracle_script"=true;
    
    type in the command prompt : sqlplus enter
    connect oracle with sys as sysdba and enter
    type the dba-password 
    
    At final execute the below script from sql prompt
    	
    @?/demo/schema/mksample <dba-password> <dba-password> password password password password password password EXAMPLE TEMP D:/app/rsrini7/product/12.1.0/dbhome_1/demo/schema/log/
    
    replace <dba-password> with the actual dba password
    
    happy learning:)
    
    

    public by zer00ne modified Jan 11, 2016  230328  1  6  1

    DB(store, schema) | Store Data in localStorage

    [FORK][ https://www.snip2code.com/Snippet/217248/object-data-store-----stuff-it-in-yer-cl
      // Local Data
       
    function DB(store, schema) {
          this.store = store;
          if (!localStorage[store] || typeof localStorage[store] == 'undefined') {
              localStorage[store] = JSON.stringify(schema|| {});
          }
          this.setData = function (key, val) {
              var lda = JSON.parse(localStorage[this.store]);
              doUpdate(ldta, key, val);
              localStorage[store] = JSON.stringify(ldta);
          };
          this.getData = function (key) {
              var ldta = JSON.parse(localStorage[this.store]);
              if (key) {
                  return pathGet(ldta, key);
              }
              return ldta;
          };
          this.reset = function (schema) {
              localStorage[this.store] = JSON.stringify(schema || {});
          };
          this.delete = function() {
              localStorage.removeItem(this.store);
          };
          var doUpdate = function (ob, kStr, val) {
              var kpth = keyStr.split('.'),
              i = 0, lstIdx = kpth.length - 1;
              for (;i < lstIdx; ++i) {
                  var key = kpth[i];
                  if (!(key in ob)) {
                      ob[key] = {};
                  }
                  ob = ob[key];
              }
              ob[kpth[lstIdx]] = val;
          };
          var pathGet = function (obj, key, leftOvrs) {
              if ( leftOvrs === undefined ) {
                  leftOvrs = key.split('.');
              }
              var cur = leftOvrs.shift();
              if ( typeof(obj) == 'object' && obj[cur] !== undefined ) {
                  if ( leftOvrs.length === 0 ) {
                      return obj[cur];
                  }
                  else if ( typeof(obj[cur]) == 'object' ) {
                      return arguments.callee(obj[cur], undefined, leftOvrs);
                  }
              }
              return null;
          };
      }
                            

    public by qxshower modified Apr 24, 2015  135568  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 augustogiles modified Feb 5, 2015  100003  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 cghersi modified Oct 11, 2015  3434  3  6  1

    Check Recovery status of DB in SQL Server 2012

    This query allows you to check the status f your recovery in SQL Server. More information on http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/
    DECLARE @DBName VARCHAR(64) = 'databasename'
     
    DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
     
    INSERT INTO @ErrorLog
    EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
     
    SELECT TOP 5
    	 [LogDate]
    	,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
    	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
    	,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
    	,[TEXT]
     
    FROM @ErrorLog ORDER BY [LogDate] DESC         

    public by cghersi modified Sep 13, 2012  2477  1  6  0

    Retrieve MySQL data, indexes and log size information

    This SQL retrieves the information regarding the actual size of the data, indexes and log files for the given schema
    select table_schema, table_name,
    concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'Mb' ) data_length_mb,
    concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'Mb' ) index_length_mb,
    concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'Mb' ) total_size_mb
    from information_schema.tables
    where table_schema like '<your_schema>'
    order by data_length desc;

    public by JKCPR modified Apr 12, 2016  2330  0  5  1

    SQL query SELECT * WHERE clause AND / OR compare conditionally

    Compare multiple values in a WHERE condition. Test that both conditions are true using the AND keyword, alternatively whether either conditions are true using the OR keyword.
    SELECT <columns> FROM <table> WHERE <condition 1> AND <condition 2> ...;
    SELECT <columns> FROM <table> WHERE <condition 1> OR <condition 2> ...;
    
    //Examples:
    SELECT username FROM users WHERE last_name = "Kong" AND first_name = "King";
    
    SELECT * FROM products WHERE category = "Insects" AND price < 100;
    
    SELECT * FROM movies WHERE title = "The Matrix" OR title = "The Blues Brothers" OR title = "2 IDs 1 Row";
    
    SELECT country FROM countries WHERE population < 3 OR population > 1;

    public by DaveMorton modified Apr 2, 2018  623  2  5  1

    connect.php - Database Abstraction Functions

    <?php
    /* 
     *  %[Filename]=[connect.php]%
     * This is a complete set of PDO abstraction functions
     * to make connecting, reading from and writing to a
     * MySQL database faster and easier. It's meant to be
     * used as a snippet in RapidPHP/WeBuilder from Karlis
     * Bluementals, but can easily be used in many other
     * web development IDEs. Items with %[something]% or
     * %[something]=[value]% are replaceable parameters
     * in RapidPHP/WeBuilder and can be swapped out with
     * whatever you like
     */
    
    /* DB Credentials */
    
    $dbh    = '%[Hostname]=[localhost]%';
    $dbn    = '%[DB Name]%';
    $dbu    = '%[Username]=[username]%';
    $dbp    = '%[Password]=[password]%';
    $dbPort = '%[DB Port]=[3306]%';
    
    $db = db_open($dbh, $dbu, $dbp, $dbn, $dbPort);
    
    /**
     * function db_open()
     * Connect to the database
     *
     * @internal param string $dbh    - db host
     * @internal param string $dbu    - db user
     * @internal param string $dbp    - db password
     * @internal param string $dbn    - db name
     * @internal param string $dbPort - db port
     * @return resource $dbConn - the database connection resource
     */
    function db_open($dbh, $dbu, $dbp, $dbn, $dbPort=3306)
    {
        try {
            $dbConn = new PDO("mysql:host=$dbh;dbname=$dbn;charset=utf8", $dbu, $dbp);
            $dbConn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $dbConn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $dbConn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
        }
        catch (Exception $e)
        {
            exit('The application has encountered a problem with connecting to the database. With any luck, the following message will help: ' . $e->getMessage());
        }
        return $dbConn;
    }
    
    /**
     * function db_close()
     * Close the connection to the database
     *
     * @internal param resource $dbConn - the open connection
     *
     * @return null
     */
    function db_close()
    {
        return null;
    }
    
    /*
     * function db_fetch_all
     * Fetches all matching rows of data from the database
     *
     * @param (string) $sql - The SQL query to execute
     * @param (mixed) $params - either an array of placeholder/value pairs, or null, for no parameters
     * @param (string) $file - the path/filename of the file that the function call originated in
     * @param (string) $function - the name of the function that the function call originated in
     * @param (string) $line - the line number of the originating function call
     *
     * @return (mixed) $out - Either the row of data from the DB query, or false, if the query fails
     */
    
    function db_fetch_all($sql, $params = null, $file = 'unknown', $function = 'unknown', $line = 'unknown')
    {
        global $dbConn;
        try
        {
            $sth = $dbConn->prepare($sql);
            ($params === null) ? $sth->execute() : $sth->execute($params);
            return $sth->fetchAll();
        }
        catch (Exception $e)
        {
            //error_log("bad SQL encountered in file $file, line #$line. SQL:\n$sql\n", 3, _LOG_PATH_ . 'badSQL.txt');
            $pdoError = print_r($dbConn->errorInfo(), true);
            $psError  = print_r($sth->errorInfo(), true);
            return false;
        }
    }
    
    /*
     * function db_fetch_row
     * Fetches a single row of data from the database
     *
     * @param (string) $sql - The SQL query to execute
     * @param (mixed) $params - either an array of placeholder/value pairs, or null, for no parameters
     * @param (string) $file - the path/filename of the file that the function call originated in
     * @param (string) $function - the name of the function that the function call originated in
     * @param (string) $line - the line number of the originating function call
     *
     * @return (mixed) $out - Either the row of data from the DB query, or false, if the query fails
     */
    function db_fetch_row($sql, $params = null, $file = 'unknown', $function = 'unknown', $line = 'unknown')
    {
        global $dbConn;
        //error_log(print_r($dbConn, true), 3, _LOG_PATH_ . 'dbConn.txt');
        try
        {
            $sth = $dbConn->prepare($sql);
            ($params === null) ? $sth->execute() : $sth->execute($params);
            $out = $sth->fetch();
            return $out;
        }
        catch (Exception $e)
        {
            //error_log("bad SQL encountered in file $file, line #$line. SQL:\n$sql\n", 3, _LOG_PATH_ . 'badSQL.txt');
            $pdoError = print_r($dbConn->errorInfo(), true);
            $psError  = print_r($sth->errorInfo(), true);
            return false;
        }
    }
    
    /*
     * function db_write
     * Writes and/or updates data to the database
     *
     * @param (string) $sql - The SQL query to execute
     * @param (mixed) $params - either an array of placeholder/value pairs, or null, for no parameters
     * @param (string) $file - the path/filename of the file that the function call originated in
     * @param (string) $function - the name of the function that the function call originated in
     * @param (string) $line - the line number of the originating function call
     *
     * @return (mixed) $out - Either the number of rows affected from the DB query, or false, if the query fails
     */
    
    function db_write($sql, $params = null, $multi = false, $file = 'unknown', $function = 'unknown', $line = 'unknown')
    {
        global $dbConn;
        try
        {
            $sth = $dbConn->prepare($sql);
            switch (true)
            {
                case ($params === null):
                    $sth->execute();
                    break;
                case ($multi === true):
                    foreach ($params as $row)
                    {
                        $sth->execute($row);
                    }
                    break;
                default:
                    $sth->execute($params);
            }
            return $sth->rowCount();
        }
        catch (Exception $e)
        {
            $pdoError = print_r($dbConn->errorInfo(), true);
            $psError  = print_r($sth->errorInfo(), true);
            error_log("bad SQL encountered in file $file, line #$line. SQL:\n$sql\nPDO Error:\n$pdoError\nSTH Error:\n$psError\nEsception Message:\n" . $e->getMessage(), 3, _LOG_PATH_ . 'db_write.txt');
            return false;
        }
    }
    • Public Snippets
    • Channels Snippets