How to Find Currently Running Query In SQL Server

Following script find out which are the queries running currently on your server.

SELECT sqltext.TEXT,
req.session_id,
req.status,req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

SQL Server stored procedure to generate random passwords

The following stored procedure creates strings of random characters based on four parameters that configure the result.

1. LEN - specifies the length of the result (required)
2. MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
3. RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
4. EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

Here is the stored procedure:

create proc [dbo].uspRandChars
    @len int=6,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go

To use the stored procedure issue commands such as the following.

declare @newpwd varchar(20)


-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd


-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd


-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd


-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd

Here is sample output from the above commands:

Next Steps

• Unsure what ASCII codes to use, refer to this list of ASCII codes
• See where this procedure can be used in your application. Some examples include users inviting their friends or generating a new password by request or as a temporary replacement.
• Determine your environment's security policy including password length and valid characters.
• Implement your password policy in the parameters.