Oct 29, 2015

Find char index in string after Nth occurrence SQL server

This function returns position or index of characters in a string after give number of occurrence (nth) of same character:

CREATE FUNCTION CharIndexAfterN (
      @Char VARCHAR(10)
      ,@String VARCHAR(MAX)
      ,@Occurance INT
      )
RETURNS INT
AS
BEGIN
      DECLARE @Index AS INT = 1

      WHILE @Occurance <> 0
      BEGIN
            SET @Index = CHARINDEX(@Char, @String, @Index + 1)
            SET @Occurance -= 1
      END

      RETURN @Index
END

For example:
SELECT dbo.CharIndexAfterN('ab', '***ab****ab*******ab*', 2)


Output: 10

Convert row and column delimited string to table in SQL Server

We can change row and field delimited string in tabular format. We are going to create a stored procedure which accept delimited data, row and column delimiter (comma, pipe, new line, colon etc) as a parameter and will return a result set:

CRETAE PROCEDURE DelimitedStringToTable (
      @Data AS VARCHAR(MAX)
      ,@RowDelimiter AS VARCHAR(5)
      ,@FieldDelimiter AS VARCHAR(5)
      )
AS
BEGIN
      DECLARE @DyQuery AS VARCHAR(MAX)

      SET @DyQuery = 'SELECT ''' + REPLACE(REPLACE(@data, @RowDelimiter, ''' UNION ALL SELECT '''), @FieldDelimiter, ''',''')

      IF RIGHT(@DyQuery, 19) = ' UNION ALL SELECT '''
            SET @DyQuery = LEFT(@DyQuery, LEN(@DyQuery) - 19)

      EXEC (@DyQuery)
END

To Execute:

EXECUTE DelimitedStringToTable '1|b|c|d;2|q||e;',';','|'

To Load output into a table:

DECLARE @DataTable TABLE(C1 INT,C2 VARCHAR(10),C3 VARCHAR(10),C4 VARCHAR(10))

INSERT INTO @DataTable
EXECUTE DelimitedStringToTable '1|b|c|d;2|q||e;',';','|'

SELECT * FROM @DataTable

Sample output:

C1
C2
C3
C4
1
b
c
d
2
q

e

Oct 28, 2015

Dynamically change database Name without using dynamic SQL in SQL Server

Sometimes there is needed to change database name of a table dynamically as passed through parameter. To do this we have to write transact SQL script as a dynamic query.   This sometimes reduced readability. Today we are going to discuss an approach to change database name or context without using dynamic SQL Query.

Let’s assume our application uses four databases:

CREATE DATABASE DB1
CREATE DATABASE DB2
CREATE DATABASE DB3
CREATE DATABASE WorkDB

Database named DB1, DB2 and DB3 has a table named Data:

USE DB1
CREATE TABLE dbo.Data(Value VARCHAR(10))
INSERT dbo.Data VALUES(DB_NAME())
GO

USE DB2
CREATE TABLE dbo.Data(Value VARCHAR(10))
INSERT dbo.Data VALUES(DB_NAME())
GO

USE DB3
CREATE TABLE dbo.Data(Value VARCHAR(10))
INSERT dbo.Data VALUES(DB_NAME())
GO

In Working Db we are going to create SYNONYM dbo.Data table which is currently pointing to DB1 database:

USE WorkDB
GO

CREATE SCHEMA DB
GO

CREATE SYNONYM DB.Data FOR DB1.dbo.Data

Now we are going to create a stored procedure which will modify the database name of synonyms:

USE WorkDB
GO
  
CREATE PROC ChangeDbNameSynonym(
      @DbName AS SYSNAME
)
AS
BEGIN
      DECLARE @DyScript AS VARCHAR(MAX)
      SET @DyScript = ''

      SELECT
            @DyScript += 'DROP SYNONYM ' +QUOTENAME(SCHEMA_NAME([schema_id])) +  '.' + QUOTENAME(name)
                  + '; CREATE SYNONYM DB.' + QUOTENAME(name)
                  + ' FOR ' + REPLACE(base_object_name, QUOTENAME(PARSENAME(base_object_name,3)) + '.',@DbName+ '.') + ';'
      FROM sys.synonyms
      WHERE [schema_id] = SCHEMA_ID('DB')

      EXEC(@DyScript)

END

Finally we are going to create procedure which accept database name as parameter and fetch data from tables in different database without using dynamic SQL:

CREATE PROC FetchData(
      @DbName AS SYSNAME
)
AS
BEGIN
      EXEC dbo.ChangeDbNameSynonym @DbName

      SELECT * FROM DB.Data

END
  
We have done!! Now we are going to verify it.

EXEC dbo.FetchData 'DB1'
EXEC dbo.FetchData 'DB2'
EXEC dbo.FetchData 'DB3'

We will get three result set as below:

Value
DB1

Value
DB2

Value
DB3


Oct 16, 2015

SQL Query to get list of all registered OLEDB provider in SQL Server

We can get list of all registered OLEDB provider in SQL Server by using system stored procedure:

EXEC [sys].[sp_enum_oledb_providers]

Or
EXEC sys.xp_enum_oledb_providers

Sample output:

Provider Name
Parse Name
Provider Description
SQLOLEDB
{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}
Microsoft OLE DB Provider for SQL Server
MSOLAP
{176941F9-18E8-47D6-860D-006FF2655608}
Microsoft OLE DB Provider for Analysis Services 9.0
MSDMine
{2CB6C2D3-DD7C-11D2-AFE4-00105A994724}
Microsoft OLE DB Provider For Data Mining Services
SQLNCLI11
{397C2819-8272-4532-AD3A-FB5E43BEAA39}
SQL Server Native Client 11.0
Microsoft.ACE.OLEDB.12.0
{3BE786A0-0366-4F5C-9434-25CF162E475E}
Microsoft Office 12.0 Access Database Engine OLE DB Provider
ADsDSOObject
{549365d0-ec26-11cf-8310-00aa00b505db}
OLE DB Provider for Microsoft Directory Services
SQLNCLI10
{8F4A6B68-4F36-4e3c-BE81-BC7CA4E9C45C}
SQL Server Native Client 10.0
Search.CollatorDSO
{9E175B8B-F52A-11D8-B9A5-505054503030}
Microsoft OLE DB Provider for Search
MSDASQL
{c8b522cb-5cf3-11ce-ade5-00aa0044773d}
Microsoft OLE DB Provider for ODBC Drivers
Microsoft.Jet.OLEDB.4.0
{dee35070-506b-11cf-b1aa-00aa00b8de95}
Microsoft Jet 4.0 OLE DB Provider
MSDAOSP
{dfc8bdc0-e378-11d0-9b30-0080c7e9fe95}
Microsoft OLE DB Simple Provider
MSDAORA
{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}
Microsoft OLE DB Provider for Oracle

Script to generate output in JSON format SQL Server

SQL Query to convert result set in JSON FORMAT in Sql server 2005, 2008, 2012

SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((
                                                            SELECT *
                                                            FROM (
                                                                 
                                                            /*<Replace With Your SELECT Statement>*/
                                                                  ) DT
                                                            FOR XML RAW
                                                            ) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '&quot;', '\"') + ']', '"},]', '"}]')


For example:

SELECT REPLACE('[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST((
                                                            SELECT *
                                                            FROM (
                                                                  SELECT *
                                                                  FROM school
                                                                  ) DT
                                                            FOR XML RAW
                                                            ) AS VARCHAR(MAX)), '<row ', '{"'), '/>', '},'), '="', '":"'), '" ', '","'), '\', '\\'), '&quot;', '\"') + ']', '"},]', '"}]')


Sample output:
[
      {"StuId":"25","StuName":"raja\"kumar","PhnNo":"k\"\"l"},
      {"StuId":"59","StuName":"ram","PhnNo":"9136784964"},
      {"StuId":"95","StuName":"hari","PhnNo":"9136784964"},
      {"StuId":"251","StuName":"raja\\kumar","PhnNo":"k\\\\l"}
]