Get the Count of User Defined Objects in SQL

SELECT 

    CASE TYPE 

        WHEN 'U' 

            THEN 'User Defined Tables' 

        WHEN 'S'

            THEN 'System Tables'

        WHEN 'IT'

            THEN 'Internal Tables'

        WHEN 'P'

            THEN 'Stored Procedures'

        WHEN 'PC'

            THEN 'CLR Stored Procedures'

        WHEN 'X'

            THEN 'Extended Stored Procedures'

    END, 

    COUNT(*)     

FROM SYS.OBJECTS

WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')

GROUP BY TYPE

Concatenate values of single column with multiple records into a single string using SQL

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ',', '') + itemRef FROM TableName Where activityRef = '123123'
Select @Names

SQL Query: find the specific number from the number series stored as record in a Column from the DB Table

//User Defined Function to Split the number series into temp results data

Create FUNCTION [dbo].[Split] (@sep char(1), @s varchar(5000))

RETURNS table

AS

RETURN (

    WITH Pieces(pn, start, stop) AS (

      SELECT 1, 1, CHARINDEX(@sep, @s)

      UNION ALL

      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)

      FROM Pieces

      WHERE stop > 0

    )

    SELECT pn AS SerialNumber,

      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 5000 END) AS IDValue

    FROM Pieces

  )

Declare @NumberToFind int= 14

Select T.Id From Table T Where ISNULL(T.ConcatStringColumn ,'') != ''

And Exists (Select IDValue From dbo.Split(',',T.ConcatStringColumn ) Where IDValue =@NumberToFind)

Output:

'Some ID Here If specific number finds from that string'

Convert String to Title Case (Proper Case) in .Net C#

C# Code (by default, English Culture):

 

CultureInfo culinfo = Thread.CurrentThread.CurrentCulture;

TextInfo txtinfo = culinfo.TextInfo;

lblResult.Text = txtinfo.ToTitleCase("AMIT JAIN");


OutPut: 
Amit Jain

Update incremental value in one colum with one update statement in SQL

Declare @CorporateId int = 12, @SortOrder int
Set @SortOrder = 0
Update DashboardIndicators SET @SortOrder = SortOrder = @SortOrder + 1 Where CorporateId = @CorporateId

Update all of the null values to default values in SQL table

 SELECT  'update '+ so.name+' set '+sc.name+'= '''' where '+sc.name+' is null '  
   
            FROM sysobjects so
            JOIN syscolumns sc ON so.id = sc.id
            JOIN systypes st ON sc.xtype=st.xtype 
            where so.type = 'U'
            and st.name in('nvarchar','int')
            and so.name = 'TableName'

Case in where clause in SQL Server

Old Approach (will be messy in case of multiple columns in where clause)

If @Id = 0
    Select * From tbl
Else
    Select * From tbl Where Id = @Id




Good Approches:

-- Do the comparison, OR'd with a check on the @Country=0 case
Select * From tbl WHERE (Id = @Id OR @Id = 0)

-- compare the Country field to itself
Select * From tbl WHERE Id = CASE WHEN @Id > 0 THEN @Id ELSE Id END