Category: SCRIPT GALLERY

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
Advertisements

How to identify table rows and size in a database in SQL Server

Problem Statement:

At times, we may need to understand the size of the tables in a database for various reasons. I often query these details to understand the growth of the data and clean up data for lower environments.

Code:
Here is a simple script to understand the rows, size of the table in a database.
You can further make changes to filter for any tables/group the results based on the indexes/partition etc.


SELECT  sc.name + '.' + t.NAME AS TableName,  
        SUM(p.[Rows]) NumerOfRecords,
        -- p.partition_number, i.index_id,i.name,
        ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, 
        ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,  
        ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB  
FROM    sys.tables t  
        INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id  
        INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id  
        INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID  
                                            AND i.index_id = p.index_id  
        INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id  
WHERE   t.type_desc = 'USER_TABLE'  
        -- Replace with valid table name if you want to filter for a table
        --AND t.NAME='YourTableName' 
GROUP BY sc.name + '.' + t.NAME,  
        i.[object_id]
        --,i.index_id, i.name, p.[Rows], p.partition_number
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC  

How to identify Process Name of a Process Id in SQL Server

We had an immediate requirement to identify process names of process ids in SQL Server. The first reaction to the requirement was to refer the program name. However, program name is customized through application, it was not the expected result.

I tried multiple ways to identify a simple solution within SQL Server, but failed to identify a straight forward solution.(You can share if you have any simpler solution.)

With the help of Powershell script, developed a small script to get the process name as below. Hope, this will be helpful for you in the similar situation.

Code:

create Table #TblProcessName(ProcessName varchar(max))

Insert into #TblProcessName
Exec xp_cmdshell 'powershell.exe "Get-Process | Select ProcessName,Id | % {$_.ProcessName + " "- + $_.Id}"' 

;With cte as
(
Select Substring(Processname,0,charindex('-',Processname)) as ProcessName, Substring(Processname,charindex('-',Processname)+1 , Len(processname)) as ProcessID 
From #TblProcessName A
)
Select * From cte A
Inner join sys.sysprocesses B on A.ProcessID = B.hostprocess

DROP Table #TblProcessName

Output:

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

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