Replace HTML Tags from SQL Text Output

We may come across some text fields in SQL table having values like '..xyz<p>asdff...', in that case we need to replace HTML tags.

To achieve that create below function and use them in SQL procedures:

CREATE FUNCTION [dbo].[RemoveHTMLTag] ( @HTMLText VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS BEGIN
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT
    SET @Start = CHARINDEX('<', @HTMLText)
    SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
    SET @Length = ( @End - @Start ) + 1
    WHILE @Start > 0 AND @End > 0 AND @Length > 0
        BEGIN
            SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')
            SET @Start = CHARINDEX('<', @HTMLText)
            SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))
            SET @Length = ( @End - @Start ) + 1
        END
    RETURN REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@HTMLText)), '$', '. '), '£', '. '), '€', '. ')
   END

In Procedure:

SELECT dbo.RemoveHTMLTag(ColumnName) FROM Table

T-SQL Script to Rebuild Indexes in a Database

T-SQL Script to rebuild indexes in a database:

SET nocount ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = Db_id();

SELECT [object_id]                  AS objectid,
       index_id                     AS indexid,
       partition_number             AS partitionnum,
       avg_fragmentation_in_percent AS frag,
       page_count
INTO   #work_to_do
FROM   sys.Dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, N'LIMITED')
WHERE  avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
       AND index_id > 0 -- Ignore heaps
       AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
  SELECT objectid,
         indexid,
         partitionnum,
         frag
  FROM   #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE ( 1 = 1 )
  BEGIN
      FETCH next FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

      IF @@FETCH_STATUS < 0
        BREAK;

      SELECT @objectname = Quotename(o.name),
             @schemaname = Quotename(s.name)
      FROM   sys.objects AS o
             JOIN sys.schemas AS s
               ON s.schema_id = o.schema_id
      WHERE  o.object_id = @objectid;

      SELECT @indexname = Quotename(name)
      FROM   sys.indexes
      WHERE  object_id = @objectid
             AND index_id = @indexid;

      SELECT @partitioncount = Count (*)
      FROM   sys.partitions
      WHERE  object_id = @objectid
             AND index_id = @indexid;

      -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
      IF @frag < 30.0          SET @command = N'ALTER INDEX ' + @indexname + N' ON '                         + @schemaname + N'.' + @objectname + N' REORGANIZE';        IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON '
                       + @schemaname + N'.' + @objectname + N' REBUILD';

      IF @partitioncount > 1
        SET @command = @command + N' PARTITION='
                       + Cast(@partitionnum AS NVARCHAR(10));

      EXEC (@command);

      PRINT N'Executed: ' + @command;
  END

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;

go

T-SQL to identify long running queries

Below queries helps to identify long running query and hanging queries:

USE MASTER
GO
SELECT SPID,ER.percent_complete,
/* This piece of code has been taken from article. Nice code to get time criteria's
http://beyondrelational.com/blogs/geniiius/archive/2011/11/01/backup-restore-checkdb-shrinkfile-progress.aspx
*/
    CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
        + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
    CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,
/* End of Article Code */    
ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE,
DB_NAME(SP.DBID) AS DBNAME,
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
        ((CASE ER.statement_end_offset
         WHEN -1 THEN DATALENGTH(est.text)
         ELSE ER.statement_end_offset
         END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
ORDER BY CPU DESC     

Restore MDF file to New Database

Execute below script to restore any MDF and LDF files to new Database.

USE [master]

GO

CREATE DATABASE DemoDB ON

( FILENAME = N'I:\MSSQL\DATA\DemoDB.mdf' ),

( FILENAME = N'K:\MSSQL\Logs\DemoDB_log.ldf' )

FOR ATTACH ;

GO

SQL to Shrink LogFile after Truncating Records

Below SQL helps you to shrink a DB grown due to truncating of records from tables regularly:

USE DemoDB;

DBCC SHRINKFILE ('DemoDB_log',0,truncateonly)

Convert Unix Timestamp to Date format

SELECT *, DATE_FORMAT(FROM_UNIXTIME(TimeStamp), '%d-%m-%Y %h:%i:%s') AS LogDate
FROM dbo.reports
ORDER BY Timestamp DESC
LIMIT 10

T-SQL to Disable all Jobs in in SQL Agent

USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

SQL to Track Running Jobs

Execute below query to find the SQL Jobs running in a server:

Exec msdb..sp_help_job @execution_status = 1

SQL Function to Convert Seconds to HH:MM

Create a function as like below:

CREATE FUNCTION [dbo].[ufn_GetHHMM] ( @pInputSecs   BIGINT )
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @HHMM       Varchar(Max)

IF @pInputSecs < 60 and @pInputSecs<>0
BEGIN
SET @HHMM= '0:01'
END
ELSE
BEGIN
SET @HHMM=ISNULL(CAST(@pInputSecs/3600 AS VARCHAR(MAX)) +':'
+RIGHT('0'+CAST(ROUND(CAST((@pInputSecs%3600.0)/60.0 as float),0) as varchar(MAX)),2),'0:00')
END

RETURN @HHMM


END

--To Get Results

SELECT dbo.ufn_GetHHMM(DurationinSeconds) --Assume you have column name 'DurationinSeconds'

T-SQL to Clean Buffers and Cache in SQL Server

T-SQL to Clean Buffers and Cache in SQL Server

DBCC FREEPROCcache
DBCC FREESystemCache('ALL')
DBCC DROPCLEANBUFFERS

Attach mdf file to Database in SQL Server

Below is the code to attach *.mdf file to existing database:

USE [master]
GO
Method 1:
EXEC sp_attach_single_file_db @dbname='BISource',
@physname=N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf'
GO
Method 2:
CREATE DATABASE BISource ON
(FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH_REBUILD_LOG
GO
Method 3:
CREATE DATABASE BISource ON
( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')
FOR ATTACH
GO

Determine Size of SQL Table

How to determine size of SQL server table:


Use built-in code: sp_spaceused ‘Tablename’

Example: sp_SpaceUsed 'Employee'

How to determine largest value comparing two or multiple columns using T-SQL

The below function can be used as a alternate for GREATEST() function in MYSQL:

Create a function with below code in your SQL SERVER database:

Below function compares 11 columns in a table.

CREATE FUNCTION dbo.fnGreatest
   (  @Value0  sql_variant,
      @Value1  sql_variant,
      @Value2  sql_variant,
      @Value3  sql_variant,
      @Value4  sql_variant,
      @Value5  sql_variant,
      @Value6  sql_variant,
      @Value7  sql_variant,
      @Value8  sql_variant,
      @Value9  sql_variant,
      @Value10  sql_variant
    )
   RETURNS sql_variant
AS
   BEGIN
     
      DECLARE @ReturnValue sql_variant

      DECLARE @MaxTable table
         (  RowID      int  IDENTITY,
            MaxColumn sql_variant
         )

        INSERT INTO @MaxTable VALUES ( @Value0 )
        INSERT INTO @MaxTable VALUES ( @Value1 )
        INSERT INTO @MaxTable VALUES ( @Value2 )
        INSERT INTO @MaxTable VALUES ( @Value4 )
        INSERT INTO @MaxTable VALUES ( @Value5 )
        INSERT INTO @MaxTable VALUES ( @Value6 )
        INSERT INTO @MaxTable VALUES ( @Value7 )
        INSERT INTO @MaxTable VALUES ( @Value8 )
        INSERT INTO @MaxTable VALUES ( @Value9 )
        INSERT INTO @MaxTable VALUES ( @Value10 )

       SELECT @ReturnValue = MAX(MaxColumn)
      FROM @MaxTable

      RETURN @ReturnValue

   END
GO








SELECT fnGreatest(date1,date2,date3.....date11)  from <tablename>

Convert Seconds to HH:MM:SS

DECLARE @SECONDS INT = 5200

SELECT CONVERT(CHAR(8),DATEADD(second,@SECONDS,0),108) 'TOS HHMMSS'

How to Apply Read/Write Mode to a Datbase

TO SET TO READ WRITE

-----------------------------------------------------------------

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*Mark the database as Read Write*/
ALTER DATABASE [DATABASE_NAM] ESET READ_WRITE WITH ROLLBACK IMMEDIATE

/*Mark it back to Multi User now*/
ALTER DATABASE DATABASE_NAME SET MULTI_USER

  

TO SET TO READ ONLY

-----------------------------------------------------------------

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*Mark the database as Read Only*/
ALTER DATABASE [DATABASE_NAME] SET READ_ONLY WITH ROLLBACK IMMEDIATE

/*Mark it back to Multi User now*/
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER

T-SQL to Filter Records Containing Special Characters and Latin Characters

The below SQL code helps to filter record containing Non-English characters:

SELECT * FROM [Table_Name]
WHERE [Column_Name] LIKE N'%[^ -~]%' collate Latin1_General_BIN

To Check SQL Server Status & Blocking Process

-- 1. To check any blocking in SQL server

SELECT CMD, * FROM SYS.SYSPROCESSES WHERE BLOCKED > 0

-- 2. Check the log space in the server

DBCC SQLPERF (LOGSPACE)

GO

  

-- 3. Check any process is running for long time.

SP_WHO2

Attaching MDF file to a Database

Below is the query to attach a .mdf file to a database

CREATE DATABASE AdventureWorks2008DWR2 ON

( FILENAME = N'C:\Users\mvaradhan\Downloads\AdventureWorksDW2008R2.mdf')

FOR ATTACH

GO

Query to get list of packages in a Integration Server

Below is the SQL query to get the list of packages deployed to an Integration Server:

WITH ChildFolders

AS

(

    SELECT PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,

        CAST('' AS SYSNAME) AS RootFolder,

        CAST(PARENT.foldername AS VARCHAR(MAX)) AS FullPath,

        0 AS Lvl

    from msdb.dbo.sysssispackagefolders PARENT

    WHERE PARENT.parentfolderid IS NULL

    UNION ALL

    SELECT CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,

        CASE ChildFolders.Lvl

            WHEN 0 THEN CHILD.foldername

            ELSE ChildFolders.RootFolder

        END AS RootFolder,

        CAST(ChildFolders.FullPath + '/' + CHILD.foldername AS VARCHAR(MAX))

            AS FullPath,

        ChildFolders.Lvl + 1 AS Lvl

    FROM msdb.dbo.sysssispackagefolders CHILD

        inner join ChildFolders ON ChildFolders.folderid = CHILD.parentfolderid

)

SELECT F.RootFolder, F.FullPath, P.name AS PackageName,

    P.[description] AS PackageDescription, P.packageformat, P.packagetype,

    P.vermajor, P.verminor, P.verbuild, P.vercomments,

    CAST(CAST(P.packagedata AS VARBINARY(MAX)) AS XML) AS PackageData

FROM ChildFolders F

    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid

ORDER BY F.FullPath ASC, P.name ASC;

Forgot or Reset SQL Authentication 'sa' password

To reset password for an SQL Authentication login user, execute the following code:

Say for example, you forgot 'sa' password, then you can execute the below code to assign new password for 'sa' user:

EXEC SP_PASSWORD @new='900%sec', @loginame='sa'.
 
Now try connecting to SQL server with your 'sa' login and new password.