Creating a Random Password in SQL Server

The following SQL Server stored procedure can be used to generate a random string which can be used in as a password.

CREATE Procedure RandomPwd @pwdLength int = 8 ,@Password varchar(30) = null output AS SET NOCOUNT ON SET ROWCOUNT @pwdLength SET @Password = '' SELECT TOP 8 @password = @password + char(number) FROM master..spt_values WHERE type='p' AND ( number between 48 and 57 OR number between 65 AND 90 OR number between 97 AND 122 ) ORDER BY newid() SELECT @Password RETURN @@error GO

SQL Server Unused Indexes

The following SQL script can be used to identify the unused indexes that have the most impact.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- create temp table. This code can be used as a template to query the current database SELECT TOP 0 DB_NAME() AS DatabaseName ,SCHEMA_NAME(o.Schema_ID) AS SchemaName ,OBJECT_NAME(s.[object_id]) AS TableName ,i.name AS indexName ,s.user_updates ,(s.system_seeks + s.system_scans + s.system_lookups) AS system_usage INTO #TempUnusedIndexes FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id -- repeat for all databases EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName ,SCHEMA_NAME(o.Schema_ID) AS SchemaName ,OBJECT_NAME(s.[object_id]) AS TableName ,i.name AS IndexName ,s.user_updates ,(s.system_seeks + s.system_scans + s.system_lookups) AS system_usage FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC' -- show results SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes

SQL Server 2008 Missing Indexes

The following SQL script uses the dynamic management views in SQL Server 2008 to determine the missing indexes which would have the biggest impact.

Impact is calculated in terms of the frequency of the index usage (scans + seeks) and the measure of query improvement (total user cost and average user impact).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 ROUND( s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0 ) AS total_cost , s.avg_user_impact , d.statement AS table_name , d.equality_columns , d.inequality_columns , d.included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY total_cost DESC

Loading Data into MySql Tables

The following ststement can be used to load data from a text file into a MySql table.

LOAD DATA INFILE 'tmp/datafile.txt' INTO TABLE schema.table FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

SQL Server Black Box Trace

A blackboc trace on SQL server captrues the last 5Mb of SQL server activity. This is useful for identifying what was running ast the time of any server crash. To start a blackbox trace

DECLARE @traceId int EXEC sp_trace_create @traceId output, 8 EXEC sp_trace_setstatus @traceId, 1

By default the trace file will be created in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA To load the trace files for inspection:

SELECT * INTO temp_trc FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\blackbox.trc', default);