Create Split Function in Sql

You have Comma separated Document and you want to split data and store into Table that time you have to create Split Function.

Steps to Create Split Function in Sql server

Step-1:- Go to Database and Expand it.

Step-2:- Go to Programmability, Expand it and Expand Functions and Go to Table-Valued Functions.

Step-3:- Create New Multiline Fuction Option.

Step-4:- Replace Code With Below Code

CREATE FUNCTION [dbo].[SplitString] 

(

-- Add the parameters for the function here

@Input NVARCHAR(MAX),

    @Character CHAR(1)

)

RETURNS @Output TABLE(

SplitTable nvarchar(1000)

)

AS

BEGIN

-- Declare the return variable here

DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1

      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character

      BEGIN

            SET @Input = @Input + @Character

      END

      WHILE CHARINDEX(@Character, @Input) > 0

      BEGIN

            SET @EndIndex = CHARINDEX(@Character, @Input)

           

            INSERT INTO @Output(SplitTable)

            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

           

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))

      END

      RETURN

END

Step-5:- After Executing Above Command, You can Find Out dbo.SplitString Function in Table-valued Function Panel. Your Split Function Successfully Created.

Step-6:- Now to Execute or Use SplitString Function using Following Command

SELECT SplitTable FROM SplitString('@InputString','@Separate Character');

For Example,

SELECT SplitTable FROM SplitString('Brij,Patel,TSE,Computer',',') Executing this Command.

List out Table's Primary Key Referencing Foreign Key Tables

If you have lots of tables in your high-end Database that time you are confusing about Primary and Foreign Key relationship, Because There is Many To Many Cross Relationship Possible. To Find out Table's Primary Key references with other tables as Foreign Key in database. You can easily List out Referencing tables of PK_Table.
For Example,

You use AdventureWorks Database. Now you want to find Foreign Key referencing of BusinessEntityID from person.Person table from AdventureWorks. Execute Following Command.

Use AdventureWorks
GO

SELECT
    c.CONSTRAINT_NAME,
    cu.TABLE_NAME AS ReferencingTable, cu.COLUMN_NAME AS ReferencingColumn,
    ku.TABLE_NAME AS ReferencedTable, ku.COLUMN_NAME AS ReferencedColumn
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
ON cu.CONSTRAINT_NAME = c.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku
ON ku.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
WHERE ku.TABLE_NAME = 'Person(PK_Table)'

You will get List of Foreign Key References like,

To Find Out Tables list From Database which Contains Specific Columnname

If You want to Figure out Particular ColumnName Used Which Tables in Database To resolve Confusion about same name. You can Do easily using Single Command
For Example,

You use Default Database AdventureWorks. and You Want to Find out "ProductId" Columnname use Which Tables in Database. You can Execute Following Command.

USE AdventureWorks2012
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%ProductID(ColumnName)%'
ORDER BY schema_name, table_name;

To Get All Table List which is used in Stored Procedure

To Get All Table List Which is be a Part of Stored Procedure, You can get all Tables List Using Single Command in Sql Server.
For Example,

If You use Default AdventureWorks Database. If You Will get Some Stored Procedure like (uspGetBillOfMaterials) Looks like

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;

    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE b.[ProductAssemblyID] = @StartProductID
            AND @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p
            ON b.[ComponentID] = p.[ProductID]
        WHERE @CheckDate >= b.[StartDate]
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25)
END;

Now Execute Following Command to Get Used Table in SP

USE AdventureWorks2012

GO

SELECT objects.name As suspected_dependencies

FROM   sys.procedures

 INNER

  JOIN sys.all_sql_modules

    ON all_sql_modules.object_id = procedures.object_id

 LEFT

  JOIN sys.objects

    ON objects.name <> procedures.name

   AND all_sql_modules.definition LIKE '%' + objects.name + '%'

WHERE  procedures.name = 'uspGetBillOfMaterials(Your Stored Procedure)'

You will get List of Tables.

To Get All Stored Procedure List which used particular Table

If You want to find out A particular Table From Database used which Stored Procedure in DB. You can Get Stored ProcedureName with Respected Schema Class.
For Example,

If you Use AdventureWorks Database. Now You want to Find out person.Person Table Used Which Stored Procedure among Database. You can use Following Commands in Sql Server.

Use AdventureWorks
GO

SELECT * FROM sys.dm_sql_referencing_entities('person.Person', 'OBJECT')

Encrypt And Decrypt ConnectionString in Web.Config File

Here We are going to see how we can encrypt and secure our connection string in our web config file. As you all know the connection string and other important information section(like smtp details) in web.config is the pillar of our data. I mean, without a connection string you just can't create an application which does some database actions like retrieving data, creating data. Here I am going to show you a demo of how we can do that, You can do the same thing in your Web API, Asp MVC, Asp.net project. I Hope you will like this.

Agenda

• Add Connection String in Web.config
• Encrypt the connection string
• Decrypt the connection string

Prequisites

• Visual Studio
• SQL Server

Add a connection String in Web.config

The connection string property must be placed under configuration section in web,config file. Here is Mine.

<connectionStrings>

       <add name="myConnection" connectionString="Data Source=BRIJ\SQLEXPRESS;Initial Catalog=ReportServer$SQLEXPRESS;Integrated Security=True" />

</connectionStrings>

Encrypt connection string

To start the Process, you must open your command window with the admin privilege. the type the following command

cd C:\Windows\Microsoft.NET\Framework\v4.0.30319 

This Command will narrate you to the framework version folder. Now Explore Project Folder Containing Folder and then copy the location. For me it is F:\TestWebApplication. Now please go back to your command propmt and type the command as follows.

ASPNET_REGIIS -PEF "connectionStrings" "F:\TestWebApplication"

After Executing Above Command in cmd You Will get Succeede Message on you cmd.

Now Check you Web.config File.

If You want to Encrypt Some Section Like smtp Protocol details. You can also it. You have to Replace "connectionStrings" with "system.net/mailSettings/smtp" and Execute Command.

ASPNET_REGIIS -PEF "system.net/mailSettings/smtp" "F:\TestWebApplication"

Decrypt connection string

You can always decrypt the connection string if you want, to decrypt just need follows the command as in the command prompt.

cd C:\Windows\Microsoft.NET\Framework\v4.0.30319 

once after you did you the above command you can execute the preceding one.

ASPNET_REGIIS -PDF "connectionStrings" "F:\TestWebApplication"

the text 'connectionStrings' is case sensitive. After Executing Command Successfully you will get your connectionStrings into Readable Version. My Decrypted connectionStrings like

<connectionStrings>

       <add name="myConnection" connectionString="Data Source=BRIJ\SQLEXPRESS;Initial Catalog=ReportServer$SQLEXPRESS;Integrated Security=True" />

</connectionStrings>

You can Decypt Smtp Section using below Command

ASPNET_REGIIS -PDF "system.net/mailSettings/smtp" "F:\TestWebApplication"