Removing pre zero from number :

select substring(Column, patindex('%[^0]%',Column), 10) from Table

Find only characters or only integer from alphanumeric value :

Find only characters from string :

DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546';
Declare @NumRange AS varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'')

 SELECT @Temp

====================================

Find only integers from string :

DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9]%', @var )
END
END

Select ISNULL(@var,0)

Find a float value from string :

DECLARE @var VARCHAR(100)='STA123.26dfg';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9.]%', @var )
END
END

Select ISNULL(@var,0)

=====================================

Connection Property : Port, IP .....

To get locl tcp port and ip address:   

select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address     is not null

To get all information related to sql connections:   

 select * from sys.dm_exec_connections


=============================================

Query Connection Properties :

SELECT
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

=============================================

When connecting locally,

Transport Protocol always comes “SHARED MEMORY” and TCP Port as NULL.

When connecting remotely,

Transport Protocol always comes “TCP” and TCP Port as negative number (if the port is set to dynamic port).

To get the actual port, just do a subtraction from a number “65536” and we get the actual port number.

Sql Installation

Code to check Sql Server Installation Date & Time :

SELECT @@SERVERNAME SERVERNAME, CREATE_DATE 'INSTALALTIONDATE'

FROM SYS.SERVER_PRINCIPALS

WHERE SID = 0X010100000000000512000000

Code For Truncating Table with foreign Key Constraints:

Use <DATABASE>
Go
/*
Created By Vimal Lohani
*/
Declare @data nvarchar(max)
-- RECREATE CONSTRAINTS
SELECT ROW_NUMBER() over(order by f.object_id) 'rno', 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+')' as Scripts
into #tempcreate
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

-- DROP CONSTRAINTS table
SELECT ROW_NUMBER() over(order by f.object_id) 'rowno','ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']' as dropscript
into #tempdrop
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
--select * from #tempdrop
--Drop Constraints process
declare @max int=(Select max(rowno) from #tempdrop)
while(@max>0)
Begin
Select  @data=dropscript from #tempdrop where rowno=@max
Exec(@data)
--PRINT @data
Set @max=@max-1
End

--Type your all Truncate Code Here

truncate table [dbo].[fact_Employee]



--Complete Truncate Code


--Create Constraints Process
declare @maxx int=(Select max(rno) from #tempcreate)
while(@maxx>0)
Begin
Select  @data=Scripts from #tempcreate where rno=@maxx
Exec(@data)
--PRINT @data
Set @maxx=@maxx-1
End

drop table #tempdrop
drop table #tempcreate

Sql Logs :

Backup Log Code :

SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Database'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
--where database_name like '<dbname>'
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_finish_date asc