Daily Archives: November 20, 2016

SQL Puzzle | How to Count total number of Rows in all databases on a Server

20 Sunday Nov 2016

Posted by in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

, ,


SQL Puzzle | How to Count total number of Rows in all databases on a Server

Write a script to Count total number of Rows in all databases on a Server

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

SET NOCOUNT ON

DECLARE @ServerName AS VARCHAR(MAX)=''
DECLARE @SQL AS VARCHAR(MAX)=''
DECLARE @dbName AS VARCHAR(1000)=''

IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL
    DROP TABLE #TempBills
       
IF OBJECT_ID('tempdb..#TempBillDetails') IS NOT NULL
    DROP TABLE #TempBillDetails

SELECT * INTO #TempBills FROM sys.databases

CREATE TABLE #TempBillDetails([NumberOfRowsIndDatabase] BIGINT)

WHILE EXISTS(SELECT TOP 1 name FROM #TempBills)

BEGIN

	SELECT TOP 1 @dbName = name FROM #TempBills
       
	SET @SQL = 'INSERT INTO #TempBillDetails
				SELECT ISNULL(SUM(rowCnt),0) [NumberOfRowsIndDatabase] FROM
				(	
					SELECT
						  SUM(p.Rows) rowCnt
					FROM 
						  ['+ @ServerName + '].' + '[' + @dbName  +'].sys.objects o INNER JOIN [' + @ServerName + '].['  + @dbName +'].sys.partitions p ON o.object_id = p.object_id
					WHERE
						  o.type = ''U'' AND o.is_ms_shipped = 0x0 AND index_id < 2
					GROUP BY 
							o.schema_id , o.name
				)l'

	EXEC (@SQL)

	DELETE FROM #TempBills WHERE name = @dbName

END

SELECT SUM(NumberOfRowsIndDatabase) NumberOfRowsIndDatabase FROM #TempBillDetails

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Remove Duplicate Word(s) from the String/Sentence

20 Sunday Nov 2016

Posted by in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 3 Comments

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Remove Duplicate Word(s) from the String/Sentence

Write a T-SQL function that removes the duplicate word(s) from the String/Sentence. Please check out the sample input and the expected output.

Sample Input

Id InputString
1 A very A long stringx, d D testing the string
2 A very A long string, d D testing the string
4 A very A long string. d D testing the string
5 Pawan Pawan

Expected Output

Id InputString OutputString
1 A very A long stringx, d D testing the string A very long stringx, d testing the string
2 A very A long string, d D testing the string A very long string, d testing the
4 A very A long string. d D testing the string A very long string. d testing the
5 Pawan Pawan Pawan

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE RemoveDuplicateWords
(	
	 Id INT
	,InputString VARCHAR(1000)                                                           
)
GO
	
INSERT INTO RemoveDuplicateWords VALUES
(1,'A very A long stringx, d D testing the string'  ),
(2,'A very A long string, d D testing the string'   ),
(4,'A very A long string. d D testing the string'   ),
(5,'Pawan Pawan'                                    )
GO

SELECT * FROM RemoveDuplicateWords
GO


--

—

SOLUTION – 1

--

CREATE FUNCTION RemoveDupicateFromString
(
@Inputs VARCHAR(1000)
,@Ignore VARCHAR(100) --Pipe Separated
)
RETURNS VARCHAR(1000)
AS
BEGIN

DECLARE @ProductTotals TABLE
(
ProductID int,
Revenue money
)
DECLARE @V AS VARCHAR(1000)
SET @V = ''
DECLARE @IgnoreChrs VARCHAR(MAX)

SET @IgnoreChrs = @Ignore
DECLARE @finalString AS VARCHAR(1000)

SET @finalString = ''
DECLARE @InputString AS VARCHAR(MAX)

SET @InputString = RTRIM(LTRIM(@Inputs))

DECLARE @Pawan TABLE ( Value VARCHAR(1) )
DECLARE @TempBills TABLE (strs VARCHAR(1000))

DECLARE @x1 AS XML
SET @x1 = CAST('<a>'+ REPLACE(@IgnoreChrs,'|','</a><a>')+ '</a>' AS XML)

INSERT INTO @Pawan
SELECT t.value('.', 'VARCHAR(1)') Value FROM @x1.nodes('/A') AS x(t)

INSERT INTO @TempBills
SELECT @InputString strs

;WITH CTE1 AS
(
SELECT SplitedValue , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM
(
SELECT CAST((''+replace(@InputString,' ' ,'')+'') as xml) xmlcol FROM @TempBills e
) s
OUTER APPLY
(
SELECT ProjectData.D.value('.', 'VARCHAR(MAX)') as SplitedValue
FROM s.xmlcol.nodes('X') as ProjectData(D)
)r
)
SELECT @finalString = STUFF((SELECT ' ' + SplitedValue
FROM
(
SELECT SplitedValue , rnk FROM
(
SELECT * , ROW_NUMBER() OVER (PARTITION BY col ORDER BY rnk) duprnk FROM
(
SELECT * , RIGHT(SplitedValue,1) r , CASE WHEN Value IS NOT NULL THEN SUBSTRING(SplitedValue,0,LEN(SplitedValue)) ELSE SplitedValue END col FROM CTE1
LEFT JOIN @Pawan p ON p.Value = RIGHT(SplitedValue,1)
)p
)m WHERE m.duprnk = 1
)y ORDER BY rnk FOR XML PATH('')),1,1,'')

RETURN @finalString

END

--

Usage


--

SELECT Id,InputString,dbo.RemoveDupicateFromString(InputString,',|;|.') OutputString FROM RemoveDuplicateWords
GO

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com