Useful SQL Queries

How to concatenate values from previous row to current row in SQL?

Answer:

Copy
DECLARE @t TABLE (
	id INT IDENTITY(1,1) PRIMARY key, 
	drink_name VARCHAR(30)
)
    
INSERT INTO @t (drink_name)
SELECT * FROM (VALUES ('milk tea'),('lemon tea'),('milk shake')) x(drink_name)
    
SELECT * FROM @t    
    
SELECT id, 
STUFF( (SELECT ', ' + drink_name  
		FROM @t t2 
		WHERE t2.id <= t1.id 
		FOR XML PATH('')),1,2,'') AS drink_name 
FROM @t t1
GO

Source

How to check the database recovery progress in SQL Server?

Copy
-- check database recovery progress
DECLARE @database_name VARCHAR(64) = 'your_database_name' --change
DECLARE @pre_text AS nvarchar(100) = '(approximately '    
DECLARE @post_text AS nvarchar(100) = ' seconds remain'

DECLARE @error_log AS TABLE (
	log_date DATETIME2(0), 
	proess_detail VARCHAR(64), 
	full_text VARCHAR(MAX)
)

INSERT INTO @error_log
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @database_name

INSERT INTO @error_log
EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @database_name

SELECT TOP 1
	log_date,
	@database_name AS database_name,
	CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN '100'
	ELSE SUBSTRING(full_text, CHARINDEX(') is ', full_text) + LEN(') is '), CHARINDEX('% complete', full_text) - (CHARINDEX(') is ', full_text) + LEN(') is ')))
	END AS percent_complete,
	CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
	ELSE SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text)))
	END AS seconds_remaining,
	CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
	ELSE CAST(CAST(SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text))) AS DECIMAL)/60 AS DECIMAL(18,2))
	END AS minutes_remaining,
	CASE WHEN SUBSTRING(full_text,1,18) = 'Recovery completed' THEN 0
	ELSE CAST(CAST(SUBSTRING(full_text, CHARINDEX(@pre_text, full_text) + LEN(@pre_text), CHARINDEX(@post_text, full_text) - (CHARINDEX(@pre_text, full_text) + LEN(@pre_text))) AS DECIMAL)/3600 AS DECIMAL(18,2))
	END AS hours_remaining,
	full_text
FROM @error_log 
ORDER BY log_date desc

How to identify similarly pronounced words in SQL server?

There are two functions in SQL Server that are used to identify whether the two strings are pronounced similarly or not.

They are

SOUNDEX() - This function takes a string as parameter and returns a four-character code. This code is called as Soundex. When this code is calculated it basically ignores the vowels (A, E, I, O, U), H, W, and Y unless they are the first letter of the string.
DIFFERENCE() - This function takes two strings as parameter and returns a integer value from 1 to 4. This function internally calculates the SOUNDEX code for each of the string and find the difference between the two SOUNDEX code.

Copy
SELECT 
SOUNDEX ('SQL') AS SQL, 
SOUNDEX ('Sequel') AS Sequel,
DIFFERENCE('SQL', 'Sequel') AS Similarity;  

SELECT 
SOUNDEX ('Michael Jackson') AS Michael_Jackson, 
SOUNDEX ('Mitchel Johnson') AS Mitchel_Johnson,
DIFFERENCE('Michael Jackson','Mitchel Johnson') AS Similarity;  

SELECT 
SOUNDEX ('Ramesh') AS Ramesh, 
SOUNDEX ('Suresh') AS Suresh,
DIFFERENCE('Ramesh','Suresh') AS Similarity;  

SELECT 
SOUNDEX ('Tamil') AS Tamil, 
SOUNDEX ('Malayalam') AS Malayalam,
DIFFERENCE('Tamil','Malayalam') AS Similarity;  

The output of the DIFFERENCE function

Output	Meaning
1	Not similar
2	Very less similar
3	Some what similar
4	Exact match/ Mostly similar

If you like this question, you may also like these…

How to concatenate multiple rows into a single string in SQL?

Write a SQL query to concatenate multiple rows into a single string?

Copy
USE TestDB;
GO

DROP TABLE IF EXISTS dbo.product_category;
GO

CREATE TABLE dbo.product_category (
	product_category_id INT NOT NULL PRIMARY KEY,
	product_category_name VARCHAR(50)
)
GO

INSERT INTO dbo.product_category VALUES (1,'Bikes');
INSERT INTO dbo.product_category VALUES (2,'Components');
INSERT INTO dbo.product_category VALUES (3,NULL);
INSERT INTO dbo.product_category VALUES (4,'Accessories');
INSERT INTO dbo.product_category VALUES (5,'Clothing');
GO

SELECT * FROM dbo.product_category;

Solution

This can be implemented in different ways.

STUFF function with XML path

We are concatenating comma with the product category name and then converting the products as single string using xml path. Then replace the leading comma with blank using stuff function.

Substring function with XML path

This is similar to the previous appraoch. But instead of replacing the leading comma, we are you using substring function to select all the text except the leading comma. We have given approximate length as 10000 and this can be modified based on the scenario.

STRING_AGG function

This string_agg function was introduced in SQL Server 2017. It concatenates the values of string and places separator(comma in our case) values between them. The separator isn’t added at the end of string. If there are NULL, then it will be ignored and separator will not be added for that.

COALESCE function

This is an old way of concatenating the rows into a string. If there is a NULL, the output will NULL as well. We should either filter out the NULL or replace a NULL with other string value. In the solution below we have replaced with a blank value and this is the reason there is two commas in the result.

Copy
-- #1 using stuff function and xml path
SELECT STUFF(
	(SELECT ',' + product_category_name
	FROM dbo.product_category
	FOR XML PATH ('')),1,1,'')

-- #2 using substring function and xml path
SELECT SUBSTRING(
	(SELECT ',' + product_category_name
	FROM dbo.product_category
	FOR XML PATH ('')),2,10000)

-- #3 using string_agg function
SELECT STRING_AGG(product_category_name,',')
FROM dbo.product_category 

-- #4 using coalesce when there is null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + COALESCE(product_category_name,'')
FROM dbo.product_category
SELECT @concat_string;
GO

-- #4 using coalesce when there is not any null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + product_category_name
FROM dbo.product_category 
WHERE product_category_name IS NOT NULL
SELECT @concat_string;
GO

Output:

How to get any object’s definition in SQL Server?

You can use the below query to get the definition of any object in SQL Server. You can replace the object type in the query to get the definition of the object.

Copy
USE AdventureWorks2019;
GO
SELECT object_name(object_id) as stored_procedure_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN (select name from sys.procedures);

SELECT object_name(object_id) as view_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN (select name from sys.views);

SELECT object_name(object_id) as trigger_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN (select name from sys.triggers);

SELECT object_name(object_id) as function_name,
definition
FROM sys.sql_modules
WHERE object_name(object_id) IN 
	(select name from sys.objects where type IN ('FN','IF','TF'));

Sample Output

How to find a SQL Server agent jobs last executed and next scheduled date time?

You can execute the below query to get the list of SQL Server agents jobs and when it was last executed and time it took to complete the job and when it is scheduled to run next.

Copy
USE msdb;

-- List out all the SQL agent job in that server with details about when it was last executed,last run duration and when it is scheduled to run next.
;WITH cte_1 AS (
	SELECT 
	sj.name AS job_name,
	sj.description AS job_description,
	sj.enabled,
	sh.step_name,
	TRY_CAST(CAST(js.next_run_date AS NVARCHAR(20)) AS DATE) next_run_date,
	STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(js.next_run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') next_run_time,
	TRY_CAST(CAST(sh.run_date AS NVARCHAR(20)) AS DATE) last_run_date,
	STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') last_run_time,
	CASE WHEN sh.run_duration > 235959 THEN
			CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR)
			+ '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2)
			+ ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
		ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
	END AS last_run_duration
	FROM msdb.dbo.sysjobs sj
	INNER JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
	LEFT JOIN msdb.dbo.sysjobschedules AS js ON sj.job_id = js.job_id
	WHERE sh.step_name = '(Job outcome)'
), cte_2 AS (
	SELECT 
	job_name,
	job_description,
	enabled,
	DATEADD(ms, DATEDIFF(ms, '00:00:00', last_run_time ), CONVERT(DATETIME, last_run_date)) AS last_run_datetime,
	last_run_duration,
	DATEADD(ms, DATEDIFF(ms, '00:00:00', next_run_time ), CONVERT(DATETIME, next_run_date)) AS next_run_datetime
	FROM cte_1
), cte_3 AS (
	SELECT 
	job_name,
	job_description,
	enabled,
	last_run_datetime,
	last_run_duration,
	next_run_datetime,
	rownum = DENSE_RANK() OVER(PARTITION BY job_name  ORDER BY last_run_datetime DESC)
	from cte_2
)
SELECT 
job_name,
job_description,
enabled,
last_run_datetime,
last_run_duration,
next_run_datetime
FROM cte_3
WHERE rownum = 1
ORDER BY last_run_datetime DESC, 
job_name ASC

GO

Output:

List of all foreign keys and referenced tables and columns

Copy
-- to get list of foreign keys and related tables and columns.
SELECT fk.name AS foreign_key_name,
pt.name AS parent_table_name,
pc.name AS parent_column_name,
ct.name AS referenced_table_name,
cc.name AS referenced_column_name
FROM sys.foreign_keys fk 
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.tables pt ON pt.object_id = fk.parent_object_id
INNER JOIN sys.columns pc ON fkc.parent_column_id = pc.column_id AND pc.object_id = pt.object_id 
INNER JOIN sys.tables ct ON ct.object_id = fk.referenced_object_id
INNER JOIN sys.columns cc ON cc.object_id = ct.object_id AND fkc.referenced_column_id = cc.column_id
ORDER BY pt.name, pc.name;

Sample Output

List out all the indexes defined on a table and column details.

Copy
USE test_db;  
GO

SELECT 
OBJECT_NAME(i.object_id) AS table_name
,i.type_desc AS index_type
,i.name AS index_name  
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id  
,ic.key_ordinal  
,ic.is_included_column  
,i.is_primary_key
,is_unique
FROM sys.indexes AS i  
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
WHERE i.object_id = OBJECT_ID('dbo.table_name');

Result:

How to replace the first instance of the string in T-SQL?

You can use the combination of stuff, charindex and len function to replace the first occurrence of the string.

Copy
declare @database_name nvarchar(max);
declare @command_text nvarchar(max);
declare @update_command_text nvarchar(max);
set @database_name = 'MY_DATABASE';


set @command_text = '
RESTORE DATABASE [MY_DATABASE] FROM  DISK = N''\\XYZABCMNO\MSSQL_Bak\MY_DATABASE.BAK''
	WITH  FILE = 1,  MOVE N''MY_DATABASE'' TO N''M:\MSSQL_Data\MY_DATABASE.mdf''
		         ,  MOVE N''MY_DATABASE_Log'' TO N''N:\MSSQL_Log\MY_DATABASE.ldf''
		         ,  NOUNLOAD,  REPLACE,  STATS = 10
GO
'
 
set @update_command_text = (select stuff(@command_text, charindex(@database_name, @command_text), len(@database_name), 'TEST_'+@database_name)) 

select @command_text, @update_command_text

How to get all the computed columns in a database in SQL Server?

Copy
-- get the list of computed columns in a database
USE your_database_name_here;
GO

SELECT schema_name(o.schema_id) AS schema_name,
object_name(c.object_id) AS table_name,
c.name AS column_name,
type_name(user_type_id) AS data_type,
definition,
is_persisted
FROM sys.computed_columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
ORDER BY schema_name, 
table_name,
column_name

How to search for a table or column in all the databases in a server

To search for a table across databases in a server

EXEC sp_MSforeachdb 
'SELECT "?" AS DB, * 
FROM [?].sys.tables 
WHERE name like ''%invoice%'''

Sample output:

To search for a column in tables across databases in a server

EXEC sp_MSforeachdb 
'SELECT "?" AS DB, object_name(object_id,db_id("?")) as TableName, * 
FROM [?].sys.all_columns 
WHERE name like ''%invoice%'''

How to check database restore history and backup file used for restore in SQL Server?

Copy
SELECT 
   rs.destination_database_name, 
   rs.restore_date, 
   bs.backup_start_date, 
   bs.backup_finish_date, 
   bs.database_name as source_database_name, 
   bmf.physical_device_name as backup_file_used_for_restore
FROM msdb.dbo.restorehistory rs
INNER JOIN msdb.dbo.backupset bs ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id 
ORDER BY rs.restore_date DESC;

How to split a string into a list of values in SQL?

I have created a function to split a string based on the delimiter value and return the result as a single column of values in a table.

Copy
CREATE OR ALTER FUNCTION dbo.fn_string_to_list (
	@input_string NVARCHAR(MAX), 
	@delimiter NVARCHAR(10)
)
RETURNS @return_table TABLE (word NVARCHAR(MAX) NULL)
AS
BEGIN
    DECLARE @position INT = 0;
    DECLARE @next_position INT = 1;
    DECLARE @word_length INT;

    WHILE @next_position > 0
    BEGIN
        SET @next_position = CHARINDEX(@delimiter, @input_string, @position + 1);
        SET @word_length = (CASE WHEN @next_position > 0 THEN @next_position ELSE LEN(@input_string) + 1 END) - @position - 1;
        INSERT INTO @return_table (word) VALUES (LTRIM(RTRIM(SUBSTRING(@input_string, @position + 1, @word_length))));
        SET @position = @next_position;
    END

    RETURN;
END

GO

Sample output:

How to search for a list of words contains in a string column?

To search whether the list of words contains in a column, you can implement it using a series of conditions with LIKE and OR operators. But to search for additional word, you need to add another condition.

Copy
SELECT * FROM dbo.my_table 
WHERE description LIKE '%word1%'
OR description LIKE '%word2%'
OR description LIKE '%word3%'

Instead of the above approach, you can create the below function dbo.fn_contains that search for the words in a string column. This function uses another function dbo.fn_string_to_list which converts a string to a list of values.

Copy
CREATE OR ALTER FUNCTION dbo.fn_contains(
    @string_to_search NVARCHAR(4000)
  , @string_to_find NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN

DECLARE @word_count INT;
DECLARE @word NVARCHAR(4000);
DECLARE @i INT = 1;

DECLARE @split_table TABLE (
	id INT IDENTITY(1,1),
	word NVARCHAR(4000)
)

INSERT INTO @split_table
SELECT word FROM dbo.fn_string_to_list (@string_to_find,' ');

SET @word_count = (SELECT COUNT(1) FROM @split_table)

WHILE @word_count >= @i
BEGIN
	SET @word = (SELECT word FROM @split_table WHERE id = @i)
	IF @string_to_search LIKE '%'+@word+'%' RETURN 1;
	SET @i += 1;
END

RETURN NULL
END

GO

Sample query to search for a list of words in a string column.

Copy
SELECT description 
FROM dbo.myTable 
WHERE dbo.fn_contains(description,'word1 word2 word3') = 1
GO 

​How to concatenate values from previous row to current row in SQL?

​How to check the database recovery progress in SQL Server?

​How to identify similarly pronounced words in SQL server?

​How to concatenate multiple rows into a single string in SQL?

STUFF function with XML path

Substring function with XML path

STRING_AGG function

COALESCE function

​How to get any object’s definition in SQL Server?

​How to find a SQL Server agent jobs last executed and next scheduled date time?

​List of all foreign keys and referenced tables and columns

​List out all the indexes defined on a table and column details.

​How to replace the first instance of the string in T-SQL?

​How to get all the computed columns in a database in SQL Server?

​How to search for a table or column in all the databases in a server

​How to check database restore history and backup file used for restore in SQL Server?

​How to split a string into a list of values in SQL?

​How to search for a list of words contains in a string column?