Get Camel Case string in sql

Hello all,

Recently i got requirement to convert string in to camel case. So, i have write simple function to convert my string into camel case.

CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END 

To call function use

SELECT DBO.CamelCase('my string convert to camel case')

OUTPUT: My String Convert To Camel Case

Sparse Column – Sql Server 2008 Features

Today, we are discuss ‘Sparse column’ features in sql server 2008.

Sparse column will not use any space for NULL value. Some advantages and disadvantages of sparse column are as follows.

Advantages :

  • Storing Null value takes up no space at all.
  • Sparse columns work really well with filtered indexes as you will only want to create an index to deal with non-null values.
  • You can add 30,000 columns as sparse column in table well regular columns has limit of 1024.

Disadvantages :

  • It will take 4 extra bytes space to store non null values in it.
  • Sparse column can’t be associated with the data types CHAR,NCHAR,IMAGE,TIME STAMP,GEOMETRY,GEOGRAPHY and USER DEFINES DATA TYPE.
  • Data compression doesn’t work.
  • You can’t apply rules.
  • Sparse doesn’t have default values.

Search Dependencies of Table in Stored Procedure/Views/Function/Trigger

There are many ways to view Dependencies of Table. One way is right-click on Table and see “View Dependencies”.

Another Way is sp_depends ‘table_name’. But sometime both way doesn’t give accurate answer. At that time following query is helpful

Declare @ParamSearchText varchar(100)
Set @ParamSearchText = 'tbl_name'
Select
     Case
            When b.type = 'P' then 'Stored Procedure'
            When b.type = 'V' then 'View'
            When b.type = 'FN' then 'Scalar Function'
            When b.type = 'IF' then 'Inline Table-Valued Function'
            When b.type = 'TF' then 'Table-Valued Function'
            When b.type = 'TR' then 'Trigger'
        Else b.type_desc
     End as ObjectType,
     c.name + '.' + b.name as ObjectName
From sys.sql_modules a, sys.objects b, sys.schemas c
Where a.[object_id] = b.[object_id]
          and b.[schema_id] = c.[schema_id]
          and a.definition like '%' + @ParamSearchText + '%'
Order by ObjectType, ObjectName

In @ParamSearchText give your Table name.

Replacing Multiple Space in SQL

There are multiple ways to remove space. We can remove space from string with help of REPLACE.

For e.g. ‘    This string has            multiple   space.      Replacing multiple        space in SQL   with REPLACE.      ‘

SELECT REPLACE(REPLACE(REPLACE(LTRIM(RTRIM('    This string has            multiple   space.      Replacing multiple        space in SQL   with REPLACE.     ')),'  ',''+CHAR(127)),CHAR(127)+' ',''),CHAR(127),'')

CHAR(127) represent the DEL.

First step is replace two space with space and special character.
In Second step we replace the special character and space to nothing.
In Third step we replace the special character to nothing.

Record count of all table

Simple sql query to count record of all tables.

SELECT 
     TBL.TABLE_NAME AS [TABLE NAME], MAX(SI.ROWS) AS [RECORD COUNT]
FROM
     SYSINDEXES SI, INFORMATION_SCHEMA.TABLES TBL
WHERE
     TBL.TABLE_NAME = OBJECT_NAME(SI.ID) AND TBL.TABLE_TYPE = 'BASE TABLE'
GROUP BY 
     TBL.TABLE_SCHEMA, TBL.TABLE_NAME