Different options to check object definition

Leave a reply

SP_HELPTEXT system stored procedure is mostly used to check the object definition like definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

OBJECT_DEFINITION – is BUILT IN function returns the source text of the specified object. It returns the definition of check constraint, default constraint, stored procedure, function, rule and views.

Option 1

EXEC sp_helptext 'Procedure Name'

Option 2

select object_definition(object_id('procedure name'))
go

Option 3

select    [definition]
from    sys.sql_modules
where    object_id = object_id('procedure name')
go

T-SQL Script to check SQL Server Instance TCP/IP Port Number

2 Replies

SQL Server Error Log

-- Check the error log for port number
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO

Executing DMV Script

-- you must connect remotely using TCP/IP
SELECT local_tcp_port
FROM   sys.dm_exec_connections
WHERE  session_id = @@SPID
GO

For Dynamic Port Number
— Read the registry for port number

-- Execute below script if SQL Server is configured with dynamic port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

For Static Port Number

-- Execute below script if SQL Server is configured with static port number
DECLARE       @portNo   NVARCHAR(10)
 
EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE',
@key        =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpPort',
@value      = @portNo OUTPUT
 
SELECT [PortNumber] = @portNo
GO

Error Fix: Microsoft SQL Server, Error: 14516

Leave a reply

Problem: Proxy (1) is not allowed for subsystem “SSIS” and user “Domain\UserName”. Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy. (Microsoft SQL Server, Error: 14516)

Solution:
Above error occurs when the user with the minimum permission (i.e. SQLAgentReaderRole and SQLAgentUserRole) or the user is configured as job owner and trying to run the job which is running under the proxy account security context.

You can execute below script to grant permission to the user and fix the error.


EXEC dbo.sp_grant_login_to_proxy
    @login_name = 'Domain\UserName',
    @proxy_name = 'Proxy Name' ;
GO

T-SQL Script to Check if LiteSpeed is installed or not on the server

Leave a reply

You can execute below script against the SQL Server instance and check whether LiteSpeed is installed on the server or not.

T-SQL script is checking registry to check the LiteSpeed version value.

declare @regkeyval varchar(20), @value varchar(255), @rc int
exec @rc=master.dbo.xp_regread 
  @rootkey= 'hkey_local_machine',
  @key='software\imceda\sqllitespeed\engine',
  @value_name='version',
  @regkeyval=@value output

set @value =isnull(@value,'-') 

if @value <> '-'
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,@value as LiteSpeedVersion 
,'LiteSpeed is installed' as LiteSpeedStautsCheck
else
select 
 @@servername as SQLInstanceName
,case serverproperty('IsClustered') when 1 then cast(serverproperty('computernamephysicalnetbios') as varchar)
 else cast(serverproperty('machinename') as varchar)
 end as WindowsServerName
,'LiteSpeed is not installed' as LiteSpeedStautsCheck

Powershell – To check the Drive and Disk Space Information

2 Replies

To get the disk space details of the server, simply go to command prompt and paste the below script it will give you disk space details.

powershell -command "& {Get-WmiObject -Class Win32_LogicalDisk -Filter 'DriveType = 3' |select PSComputerName, Caption,@{N='Capacity_GB'; E={[math]::Round(($_.Size / 1GB), 2)}},@{N='FreeSpace_GB'; E={[math]::Round(($_.FreeSpace / 1GB), 2)}},@{N='PercentUsed'; E={[math]::Round(((($_.Size - $_.FreeSpace) / $_.Size) * 100), 2) }},@{N='PercentFree'; E={[math]::Round((($_.FreeSpace / $_.Size) * 100), 2) }}}"

Sample Output
Caption : C:
Capacity_GB : 283.44
FreeSpace_GB : 51.48
PercentUsed : 81.84
PercentFree : 18.16