How to remove space in a column names of table in SQL Server

Here is a script to rename the column names to avoid the space(s) in the column name.


create table test_table([column test space] int)

select * From test_table

--Script to do the change in column name

Declare @SQL nvarchar(MAX)=''
Set @SQL =(
select ';EXEC sp_rename '''+ B.name+'.'+a.name +''', '''
+replace(a.name,' ','') +''', ''COLUMN'''  
from sys.columns A
Inner join sys.tables B on A.object_id = B.object_id and  
OBJECTPROPERTY(b.object_id, N'IsUserTable') = 1
where 
 system_type_id in 
(select system_type_id From sys.types ) and charindex(' ',a.name)!=0
FOR XML path(''))

print @SQL
/*exec( @SQL) This has been commented, verify the results and execute*/
--check the column names
select * From test_table
--Cleanup the table
Drop table test_table
Advertisements

How to get last running query based on SPID in SQL Server

We often need to identify the SQL text that was executed last time for a spid in SQL Server. Recently one of my friend was asking to get these information for his troubleshooting purpose. I have come up with a very simple query to help him as below.

Hope this will be helpful for those looking for similar information.


SELECT
sysprc.spid,sysprc.waittime,sysprc.lastwaittype,DB_NAME(sysprc.dbid) AS database_name,
sysprc.cpu,sysprc.physical_io,sysprc.login_time,sysprc.last_batch,sysprc.status,
sysprc.hostname,sysprc.[program_name],sysprc.cmd,sysprc.loginame,
OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text
FROM sys.sysprocesses sysprc
OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt

How to track SQL Server Database connections using T-SQL

One of my friend was asking to get him information from SQL Server to troubleshoot some performance issues.
My initial understanding, to collect data from the Performance counters, however, I realized that my friend is looking for information at grouping HostName, ProgramName etc.

Hence, come up with a small script as below to capture the details every 1 minute(customized as per the request).


If exists(Select 1 From sys.tables where name='Capture_DBConnection')
Drop table Capture_DBConnection

Create Table Capture_DBConnection (CapturedTime datetime,SPID Varchar(MAX), [Status] varchar(50),Login nvarchar(100),HostNAme varchar(5000),BlkBy Varchar(MAX), DBNAME sysname null,Command varchar(MAX),CPUTIME Varchar(MAX), DISKIO Varchar(MAX),
LastBatch Varchar(MAX), ProgramName Varchar(MAX),SPID1 Varchar(MAX), REQUESTID Varchar(MAX))

While(1=1)
Begin
	Create Table #Capture_DBConnection (SPID Varchar(MAX), [Status] varchar(50),Login nvarchar(100),HostNAme varchar(5000),BlkBy Varchar(MAX), DBNAME sysname null,Command varchar(MAX),CPUTIME Varchar(MAX), DISKIO Varchar(MAX),
		LastBatch Varchar(MAX), ProgramName Varchar(MAX),SPID1 Varchar(MAX), REQUESTID Varchar(MAX))
	
	Insert into #Capture_DBConnection Exec Sp_who2
	Insert into Capture_DBConnection 
	Select getdate(),* from #Capture_DBConnection 
	Drop table #Capture_DBConnection
	Waitfor delay '00:01:00'
END

Usage:
Here are few examples how to use the data collected.


-- Row data
Select * From Capture_DBConnection

-- To get count of connection for specified group
Select CapturedTime, HostName, ProgramName, Count(CapturedTime)
From Capture_DBConnection
Group by CapturedTime, HostName, ProgramName

Hope, this script will help you if you come across such situation.

How to identify the referenced objects of an object in SQL Server

Here is a very small script that helps you to identify the referenced objects of an object in SQL Server.


Declare @ParentObject_name sysname = 'YOUR OBJECT'
;With cte as (
Select OBJECT_NAME(referencing_id) Parent_ObjectName,referenced_entity_name Referenced_ObjectName,
		ISNULL((Select type From sys.objects where name = SED.referenced_entity_name),'**')  ObjType
From sys.sql_expression_dependencies SED where referencing_id = object_id(@ParentObject_name)
union All
Select OBJECT_NAME(referencing_id),SED.referenced_entity_name,
		ISNULL((Select type From sys.objects where name = SED.referenced_entity_name),'**')  ObjType
From sys.sql_expression_dependencies SED 
Inner join cte c On SED.referencing_id = object_id(c.Referenced_ObjectName) 
Where OBJECT_NAME(referencing_id)  SED.referenced_entity_name and c.ObjType Is NOT NULL
)
Select * From cte  

HASHBYTES for a large string in SQL Server

HASHBYTES function in SQL Server is used to hash the input using the algorithm specified.

This is a very efficient method to compare the string, for an example, lets compare the view definition between two different databases etc.

I do not really want to cover the usage or basic information of HASHBYTES in this post, but, a limitation and how to overcome the same. The first input parameter of HASHBYTES is the algorithm that needs to be used and the second one is the actual character or binary string that needs to be hashed.Now, the limitation is it will not accept the string more than 8000 bytes.

Lets quickly look at the example.


CREATE TABLE dbo.Test1 (c1 nvarchar(MAX));  
Insert into dbo.Test1 Select Replicate('a',4001) 
Select hashbytes('SHA1',c1) From dbo.Test1
Drop table dbo.Test1

The above code will throw an exception “String or binary data would be truncated.” as below:

To overcome the limitation, I have come up with a solution to break down the string into multiple and apply the hashing separately and later combined.

The script is as below:


Create FUNCTION [dbo].[GenerateHASHforLargeValue]
(   
    @TextValue nvarchar(max)
)

RETURNS varbinary(20)

AS
BEGIN

    if @TextValue = null
        return hashbytes('SHA1', 'null')

    Declare @TextLength as integer
	Declare @BinaryValue as varbinary(20)

    Set @TextLength = len(@TextValue)
	Declare @LenCount int  = 3500
    if @TextLength > @LenCount
    Begin
    ;With cte 
    as
    (
	Select substring(@TextValue,1, @LenCount) textval, @LenCount+1 as start, @LenCount Level,
               hashbytes('SHA1', substring(@TextValue,1, @LenCount)) hashval
	Union All 
	Select substring(@TextValue,start,Level), start+Level ,@LenCount  Level, 
               hashbytes('SHA1', substring(@TextValue,start,Level) + convert( varchar(20), hashval )) 
	From cte where Len(substring(@TextValue,start,Level))>0
    ) Select @BinaryValue = (Select Top 1 hashval From cte Order by start desc)
			return @BinaryValue
    End
    else
    Begin
	Set @BinaryValue = hashbytes('SHA1', @TextValue)
	return @BinaryValue
    End
    return null
END

If we apply the above function, then it will generate the hash code without the string truncation issue.

Hope this will help you whenever you may need to generate hash for larger strings!!!

How to extract ONLY numbers from a string in SQL Server

Here is a script to extract *ONLY* numbers from a string.


DECLARE @Sample TABLE(string VARCHAR(2000));
INSERT INTO @Sample
Values( 'hhjjj12345hhhkk'),('-7655'),('asd5-5dffgdfg105'),('hhjjj12345hhhkk'),('a1b2c3d4ee55fff666gggg7777h'),('Al1')
,('2Bob$'),('!Carl%'),('%D#?:"{}|\][+_)(*&^	%$#@!~`_+?>a*vi()(d&*(&*#?:"{}|\][J+o_n)es(*&^%$#@!~`_+?><,./;:"' ),('    Eddie     ')
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
FinalOutput AS (
SELECT string, (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
                       THEN SUBSTRING(string, n, 1)
                      ELSE ''
                  END + ''
           FROM Nums
           WHERE n <= LEN(string)
           FOR XML PATH('')) AS stringout
FROM @Sample)
SELECT string, stringout FROM FinalOutput;

How to replace first occurrence of a word in a sentence in SQL Server

Have you ever thought of How REPLACE is working? It just replaces all the occurrences of a word in the sentence.

Lets look at an example.


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')
Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	Replace(string,@searchstring,@replacestring)
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

In the above example, there are two occurrences of the word “was” for the first record. And the REPLACE function results in replacing all occurrences.

Suppose, if you want to only replace the first occurrence, How do you do?

First approach with CROSS APPLY and SUBSTRING:


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

select id,string,
	case when Search1.Pos != 0 then 
				substring(string, 0,Search1.Pos )+ @Replacestring +
				substring(string, Search1.Pos+LEN(@searchstring), LEN(string) )
	else string end ChangedText
	from #StringTable
		cross apply (select (charindex(@searchstring, string))) as Search1(Pos)

Drop table #StringTable

Another method with STUFF and PATINDEX:


CREATE TABLE #StringTable ( id INT, string VARCHAR(128) )

INSERT #StringTable VALUES (1,'It was a pleasant morning, and he was the only kid in the garden')
,(1,'There is no match word')

Declare @searchstring varchar(100)='was',@Replacestring varchar(100)='is'

Select id, string, Case when charindex(@searchstring, string) != 0 Then
				Stuff(string, patindex('%'+@searchstring+'%',string),len(@searchstring),@Replacestring) 
				Else string End ChangedText
	from #StringTable

Drop table #StringTable