Category: SCRIPT GALLERY

Search a value in your database in SQL Server

Here is a script to identify a value in your database. The script will identify the presence of the search value from all tables and generate select queries from the respective tables which you can execute and confirm.

	DECLARE @STRINGTOLOOKFOR VARCHAR(500)
		,@TABLENAME SYSNAME
		,@FULLTABLENAME SYSNAME
        --<-- Give the value to search------->
	SELECT @STRINGTOLOOKFOR = '7F0B0DD5-22BC-421B-9416-3A7C24146A98'  
        --<-- Give the value to search------->

	DECLARE @COLUMNNAME NVARCHAR(128),
		@DATETYPE NVARCHAR(128),
		@ROW SMALLINT,
		@ROWCOUNT INT,
		@SQL NVARCHAR(1000)
		
	DECLARE STRING_FIND_CURSOR CURSOR FAST_FORWARD FOR 
		
	SELECT TABLE_NAME, TABLE_SCHEMA+'.'+TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_TYPE ='BASE TABLE' 
	
	OPEN STRING_FIND_CURSOR
	
	FETCH NEXT FROM STRING_FIND_CURSOR 
	INTO @TABLENAME, @FULLTABLENAME
	
	SET @STRINGTOLOOKFOR = @STRINGTOLOOKFOR 
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @ROW = 1
	
		SELECT @ROWCOUNT = MAX([ORDINAL_POSITION])
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] = @TABLENAME 
		GROUP BY [ORDINAL_POSITION]
	
		WHILE @ROW  <= @ROWCOUNT
                BEGIN 
		SELECT @COLUMNNAME =  QUOTENAME(COLUMN_NAME) ,
			@DATETYPE = [DATA_TYPE]
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] = @TABLENAME 
			AND [ORDINAL_POSITION] = @ROW
		ORDER BY [ORDINAL_POSITION]
	
		SET @ROW = @ROW + 1  
			
		SET @SQL = NULL
	
		IF @DATETYPE IN ( N'CHAR', N'VARCHAR', N'TEXT')
			SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE PATINDEX(''%' + @STRINGTOLOOKFOR + '%'', ' + @COLUMNNAME + ') > 0'
			IF @DATETYPE IN (N'UNIQUEIDENTIFIER') AND @STRINGTOLOOKFOR LIKE '[A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][-][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9][A-F,0-9]'
				SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE' + @COLUMNNAME + ' = ''' + @STRINGTOLOOKFOR + ''''
			IF @DATETYPE IN (N'NCHAR', N'NVARCHAR', N'NTEXT')
				SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE PATINDEX(''%' + @STRINGTOLOOKFOR + '%'', CAST(' + @COLUMNNAME + ' AS TEXT)) > 0'
			
			IF @DATETYPE IN (N'SQL_VARIANT',N'SMALLINT',N'INT',N'BIGINT',N'TINYINT')
				SET @SQL = 'SELECT * FROM ' + @FULLTABLENAME + ' WHERE CONVERT(VARCHAR(8000),' + @COLUMNNAME + ') LIKE ''%'+ @STRINGTOLOOKFOR + '%'''
			
			IF @SQL IS NOT NULL
			BEGIN
				SET @SQL = 'IF EXISTS(' + @SQL + ') PRINT ''SELECT '+@COLUMNNAME+' FROM ' + @FULLTABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE ''''%'+@STRINGTOLOOKFOR+'%'''''''
				EXEC (@SQL)
			END
		END
	
		FETCH NEXT FROM STRING_FIND_CURSOR 
		INTO @TABLENAME, @FULLTABLENAME

	END
	
	CLOSE STRING_FIND_CURSOR
	DEALLOCATE STRING_FIND_CURSOR
	
	

How to build comma separated string in SQL Server

Building a comma separated string is a very frequent&common requirement for SQL Server developers. Developers find different ways to achieve this like CLR/XML PATH/Co-related queries etc. With this post, we are going to see two options that are widely used XML PATH & string_AGG and a quick look at its performance comparison.

— Using STUFF & XML PATH

This is the most widely used method to build the comma separated string. In my personal experience, I observed many performance issues related to this method for a large set of data.


Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')

SELECT  SessionID,STUFF((SELECT  ',' + CourseID FROM BuildCommaSeparatedString EE
            WHERE  EE.SessionID=E.SessionID
            ORDER BY CourseID -- To order the Courses in order 
        FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM BuildCommaSeparatedString E
GROUP BY E.SessionID


Sample Execution results — Using string_AGG in SQL Server 2017

With SQL Server 2017 release, there is a better way to do this using string_agg built-in function. The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator.

STRING_AGG ( input_string, separator ) [ order_clause ]

input_string - is the column name of any type that can convert to a comma separated string
separator - is the separator caharacter like , or ; etc
[ order_clause ] - specifies the sort order of concatenated results using WITHIN GROUP clause

WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )
Please note that, The STRING_AGG() ignores NULL and it does not add the separator for NULL when performing concatenation.

Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))

Insert into BuildCommaSeparatedString Values 
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')

Select SessionID, STRING_AGG(CourseID,',') From BuildCommaSeparatedString Group by SessionID

--To build the comma separated string in an order way
Select SessionID, STRING_AGG(CourseID,',') within group (Order by CourseID asc) 
From BuildCommaSeparatedString Group by SessionID

Sample Execution results A quick look on Performance benefit

The below snapshot clearly indicates that the performance benefit of string_AGG built-in function over XML PATH approach.

Conclusion

If you are in SQL Server 2017 – use built-in function string_AGG instead of any other method. I have seen this outperform many times than a custom built code. If you do not agree or have any different experience, please share the specific case with an example for the benefits of readers. Thanks in advance!

How to remove special characters like TAB, Carriage Return, and Line Feed characters from string in SQL Server

Today, we are going to see a scenario where we need to remove special characters from a string in SQL Server. Let us discuss on few methods to achieve this “extended” trim functionality.

1. Option: Using REPLACE

REPLACE function can be effectively used to replace the special characters.

Script:

DECLARE @BaddataString NVARCHAR(max);
SET @BaddataString = 'my '+
			CHAR(10)+CHAR(10)+CHAR(10) +
			 'text   ' + 
			CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) + 
			 '     ' +
			CHAR(10)+CHAR(10)+CHAR(10);

SELECT 
           @BaddataString,
           LEN(@BaddataString)
           , REPLACE(REPLACE(REPLACE(RTRIM(@BaddataString),CHAR(9), ''),CHAR(10),''),CHAR(13),'') ,
           LEN(REPLACE(REPLACE(REPLACE(RTRIM(@BaddataString),CHAR(9), ''),CHAR(10),''),CHAR(13),''))


Here is the output of the below code:

2. Option: Using CDATA Another effective option to use CDATA method.

Script


/*
The below function replaces special characters like invisible TAB, Carriage Return, and Line Feed characters.
*/
CREATE FUNCTION dbo.FN_RemoveBadCharacter(@input VARCHAR(MAX))
   RETURNS VARCHAR(MAX)
AS
BEGIN 
   RETURN (SELECT CAST('' AS XML).value('(/r/text())[1] cast as xs:token?','VARCHAR(MAX)'));
END
GO
DECLARE @BaddataString NVARCHAR(max);
SET @BaddataString = 'my '+
						CHAR(10)+CHAR(10)+CHAR(10) +
					 'text   ' + 
						CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32) + 
					 '     ' +
						CHAR(10)+CHAR(10)+CHAR(10);

SELECT		@BaddataString, 
		LEN(@BaddataString)
			, dbo.FN_RemoveBadCharacter(@BaddataString),
		LEN(dbo.FN_RemoveBadCharacter(@BaddataString))

Here is the output of the below code:

3. Option: Using CLR to define a function to remove

There are certain things its best to do at application code rather than T-SQL. SQL Server may not be the best place to formatting string, date etc. There will be lots of available resources just away from a google search.

Hope, you enjoy this post, please share your thoughts as always!

Generate Random characters in SQL Server

Here is a script to generate randon characters in SQL Server

It also allows you to generate the characters in random as well as required format. There are four types of format the function supports:

Formats:
1. ‘Proper’ – proper name form (i.e. Xxxxx)
2. ‘Upper’ – all uppercase (i.e. XXXXX)
3. ‘Lower’ – all lowercase (i.e. xxxxx)
5. ‘Mixed’ – randomly mixed case (i.e. xXxxxXXxx)


Create View dbo.Q_Random as 
	Select Rand() as [RandomNumber]
GO
Create Function dbo.fn_GenerateText 
				(@Length integer, @Format varchar(6) = 'Mixed')
Returns varchar(256)
as 
Begin

--	Formats:	
--		'Proper' - proper name form (i.e. Xxxxx)
--		'Upper'  - all uppercase (i.e. XXXXX)
--		'Lower'  - all lowercase (i.e. xxxxx)
--		'Mixed'  - randomly mixed case (i.e. xXxxxXXxx)
--		 null    - randomly mixed case (i.e. xXxXxxxxxX)
--

Declare 
	@RandomValue             varchar(256), 
	@Count                   integer,
	@RandomNumber            float, 
	@RandomNumberInteger     integer, 
	@CurrentCharacter        char(1),
	@ValidCharactersLength   integer,
	@ValidCharacters         varchar(255) 

Set @RandomValue = '';

If (@Length = 0) 
	Goto ReturnData 


If (@Format = 'Mixed') 
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; 
else
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';


Set @ValidCharactersLength = Len(@ValidCharacters); 
Set @CurrentCharacter      = ''; 
Set @RandomNumber          = 0; 
Set @RandomNumberInteger   = 0; 

Set @Count = 1; 


While @Count <= @Length 
Begin 
	Set @RandomNumber = (Select RandomNumber from Q_Random); 

	Set @RandomNumberInteger = Convert(integer, ((@ValidCharactersLength - 1) * @RandomNumber + 1)); 
 
	Set @CurrentCharacter = SubString(@ValidCharacters, @RandomNumberInteger, 1); 

	Set @RandomValue = @RandomValue + @CurrentCharacter; 

	Set @Count = @Count + 1; 
End 


If @Format = 'Lower' 
	Set @RandomValue = Lower(@RandomValue); 

If @Format = 'Upper' 
	Set @RandomValue = Upper(@RandomValue); 

If @Format = 'Proper' 
	Set @RandomValue = Upper(Left(@RandomValue, 1)) + Substring(Lower(@RandomValue), 2, (@Length - 1));  

--	... or the default  gives random `casing`, and 'Mixed' gives random alphanumeric `casing`


ReturnData:

Return @RandomValue 

End 

Sample Executions:

Select dbo.fn_GenerateText(10,’Lower’)
Select dbo.fn_GenerateText(10,’Upper’)
Select dbo.fn_GenerateText(10,’Mixed’)–default value
Select dbo.fn_GenerateText(10,’Proper’)

Cleanup of Load Runner Test data from SQL Server

Here is a script to purge the Load test data from your load runner database. This would be handy at times like running of disk space or performance issues related to load runner etc.


--Provide your Load runner database name
USE <<Loadrunner DBName>>
Create proc SQLZealot_CleanupTestData  (@DeleteDateUpto Datetime)
with encryption
as
Begin

	If not exists(Select 1 From sys.tables where name = 'TempTobeDeletedLoadRecords')
		Select LoadtestrunID into TempTobeDeletedLoadRecords from Loadtestrun (nolock) where StartTime < @DeleteDateUpto

	Declare @LoadtestrunID bigint
	While exists(Select 1 From TempTobeDeletedLoadRecords )
	Begin
		Set @LoadtestrunID = (Select Top 1 Loadtestrunid from TempTobeDeletedLoadRecords ORder by 1 asc)
		Exec Prc_DeleteLoadTestRun @Loadtestrunid
		Delete TempTobeDeletedLoadRecords Where Loadtestrunid = @LoadtestrunID
	End

	Drop Table TempTobeDeletedLoadRecords

End

How do we identify LOB datatype columns in SQL Server

Large OBject (LOB) data types are datatypes that can store large data. These datatypes can hold values larger than 8K row size.Prior to SQL Server 2005, we have only TEXT, NTEXT and IMAGE to hold large data. With SQL 2005, Microsoft has introduced additional three datatypes to hold large values like VARCHAR(MAX),NVARCHAR(MAX) and VARBINARY(MAX).

How to identify LOB columns in your database?

The below query is useful to identify LOB columns. Please note that the query contains a filter not to fetch CDC schema objects. Feel free to add more filters as required.


SELECT T.TABLE_CATALOG DATABASE_NAME,T.TABLE_SCHEMA AS SCHEMA_NAME,
    T.TABLE_NAME, 
    C.COLUMN_NAME,C.DATA_TYPE DATA_TYPE,C.CHARACTER_MAXIMUM_LENGTH MAXIMUM_LENGTH,C.COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN INFORMATION_SCHEMA.TABLES T
        ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE' 
AND ((C.DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND C.CHARACTER_MAXIMUM_LENGTH = -1)
OR DATA_TYPE IN ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM'))
AND T.TABLE_SCHEMA NOT IN('CDC') -- EXCEPTION LIST
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME

How do we identify the length or size of LOB datatype in SQL Server

At times, you may need to understand the length or size of your LOB columns. One example is to set a right value for “max text repl size (B)” to avoid certain issues like this. Usually, we use a function LEN to get the length of the data, however, LEN is not compatible certain datatypes like TEXT,NTEXT and IMAGE.


To resolve, SQL Server has another function – DATALENGTH(). The function returns the number of length or size of the data in bytes. The below query will help to identify the length or size of LOB datatypes like TEXT, NTEXT, IMAGE etc.

--Table creation script
Create Table tablename (columnname image)

--Sample data insert (The data has been trimmed for readability)
Insert into tablename values('0xFAEFCD8FFE000104A46494600010100000100010000FFDB00840009060')

SELECT  DATALENGTH(columnname) as inBytes,
        DATALENGTH(columnname) / 1024.0 as inKb,      
        DATALENGTH(columnname) / 1024.0 / 1024.0 as inMb FROM   tablename

DROP Table tablename


Thats it with this, hoping to see you with another blog post soon!

Decoding hexa error code to windows error message in SQL Server

Problem Statement:
Sometimes, the error messages generated are not friendly to understand the underlined issues. This is going to be an extra effort to google out for most of us with the error code (mostly a hexa value) and identify the corresponding windows error message.

Solution:
A simple solution would be to use NET HELPMSG with the errocode. But if the error code is a hexa value, then we need to identify the corresponding Decimal value to use as a parameter to NET HELPMSG.

Here is a script to identify actual error message from hex error code. This is important to be aware that this approach will only work for Win32 error codes from Microsoft Windows. If the net helpmsg command does not return a result, the cause is likely the error has not originated from Windows, or no longer a valid Win32 process.

DECLARE @hex VARCHAR(64) = '0x80070643'
Set @hex = Right(@hex,4)

DECLARE @rez BIGINT;
SELECT @rez = ISNULL(@rez,0) * 16 + 
CHARINDEX(substring(@hex,n.number+1,1),'0123456789ABCDEF') - 1
FROM MASTER..spt_values n WHERE n.TYPE='P' AND n.number<len(@hex)

/*
	--To use NET HELPMSG, need to enable xp_cmdshell as below:

	EXEC sp_configure 'show advanced options',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'xp_cmdshell',1
	RECONFIGURE WITH OVERRIDE
	EXEC sp_configure 'show advanced options',0
	RECONFIGURE WITH OVERRIDE
*/

Declare @Table Table(WindowsErrorMessage nVarchar(MAX))
Declare @s nvarchar(500) = 'NET HELPMSG ' + Cast(@rez as varchar(MAX))
Insert into @Table 
exec xp_cmdshell @s

Select @hex HexaVal,@rez DecimalVal,* From @Table 
where WindowsErrorMessage is not null