Useful SQL Queries

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

-- 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

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;  

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;

-- #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

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'));

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

-- 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;

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');

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

-- 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

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

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

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;

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

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

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

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