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:

Check if a Perticular Database exist or not

This is simple sql query:

USE master
Go


IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   name = 'MyDataBase' ) 
    PRINT 'Exists'
ELSE 
    PRINT 'Does Not Exists'