Parsing Comma Delemeted String in Sql

I will just go with the Example as its Self Exclamatory.

Sql Query:

declare @string varchar(500)
set @string = 'ABC,DEF,GHIJK,LMNOPQRS,T,UV,WXY,Z'

declare @pos INT
declare @piece varchar(500)

-- Need to tack a delimiter onto the end of the input string if one doesn’t exist
if right(rtrim(@string),1) <> ','
set @string = @string + ','

set @pos = patindex('%,%' , @string)
while @pos <> 0
begin
set @piece = left(@string, @pos - 1)

-- You have a piece of data, so insert it, print it, do whatever you want to with it.
print cast(@piece as varchar(500))

set @string = stuff(@string, 1, @pos, '')
set @pos = patindex('%,%' , @string)
end

Output:


 

A stored Procedure to Count Numbers of Rows in Table

This a simple Stored procedure to count the total numbers of rows in each table of your Database.
I assume you have Northwind database installed.

The Stored Procedure

IF EXISTS ( SELECT  *
FROM sysobjects
WHERE id = OBJECT_ID(N'[dbo].[sp_GetRowsCountForAllTables]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
DROP PROCEDURE [dbo].[sp_GetRowsCountForAllTables]
GO

CREATE PROCEDURE sp_GetRowsCountForAllTables
@DBName VARCHAR(128) = NULL
AS
SET nocount ON
IF @DBName IS NULL
SET @DBName = DB_NAME()

CREATE TABLE #a
(
TableName VARCHAR(128)
, norows INT NULL
, id INT IDENTITY(1, 1)
)

DECLARE @id INT
, @maxID INT
, @TableName VARCHAR(128)
, @FKName VARCHAR(128)
, @cmd NVARCHAR(1000)
, @rc INT
, @spcmd VARCHAR(1000)

SET @cmd = 'exec ' + @DBName + '..sp_executesql N''insert #a (TableName)
select TABLE_NAME from information_schema.tables
where TABLE_TYPE = ''''BASE TABLE'''' ''
'
EXEC (@cmd)

SELECT @id = 0
, @maxID = MAX(id)
FROM #a

WHILE @id < @maxID
BEGIN
SELECT @id = MIN(id)
FROM #a
WHERE id > @id

SELECT @TableName = TableName
FROM #a
WHERE id = @id

SET @cmd = 'exec ' + @DBName
+ '..sp_executesql N''update #a set norows = (select rows from sysindexes where indid in (0,1) and id = object_id('''''
+ @TableName + '''''))'
SET @cmd = @cmd + ' where #a.id = ' + CONVERT(VARCHAR(10), @id)
+ ''''

EXEC (@cmd)
IF @rc <> 0
OR @@error <> 0
BEGIN
RAISERROR('failed %s',16,-1,@TableName)
RETURN
END
END

SELECT *
FROM #a ORDER BY norows desc

DROP TABLE #a
GO

The Output:

EXEC sp_GetRowsCountForAllTables

 

Finding Duplicates in a Table 's Column

I assume You have Northwind Database.

I will be using The Employee Table.

As Show in the figure there are 6 Selected cells which are the Duplicates( Just assume it)
I want to find this number ( i mean 6.)

The query for the above is:

SELECT  Title
, COUNT(Title) AS NumOccurrences
FROM dbo.Employees
GROUP BY Title
HAVING ( COUNT(Title) > 1 )

SELECT *
FROM dbo.Employees

 

Finding Total Number of Rows, Columns in a Database and Tables Sizes

Here is the Script:

USE Northwind 
GO
CREATE TABLE #temp
(
table_name SYSNAME
, row_count INT
, reserved_size VARCHAR(50)
, data_size VARCHAR(50)
, index_size VARCHAR(50)
, unused_size VARCHAR(50)
)

SET NOCOUNT ON

INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name
, a.row_count
, COUNT(*) AS col_count
, a.data_size
FROM #temp a
INNER JOIN information_schema.columns b ON a.table_name COLLATE database_default = b.table_name COLLATE database_default
GROUP BY a.table_name
, a.row_count
, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS INTEGER) DESC

DROP TABLE #temp

Output:

 
%d bloggers like this: