Adding new assembly and functions

1. Check if there is any previous assembly exists. If there is any, then remove it.

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

2. Copy dll file to server (eg: F:\Parvez\Msdn.SqlRegex.dll')

3. Create assembly in database.

                    create assembly "Msdn.SqlRegex" from 'F:\Parvez\Msdn.SqlRegex.dll'

4. Create sql object using that assembly.
   
USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatch]    Script Date: 10/28/2014 16:34:10 ******/
CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatch]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatch.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=11 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'


USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroup]    Script Date: 10/28/2014 16:34:48 ******/
CREATE FUNCTION [dbo].[RegexGroup](@input [nvarchar](max), @pattern [nvarchar](4000), @name [nvarchar](4000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroup]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroup.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=9 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroups]    Script Date: 10/28/2014 16:35:31 ******/
CREATE FUNCTION [dbo].[RegexGroups](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Group] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroups]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroups.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=80 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'



USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatches]    Script Date: 10/28/2014 16:36:02 ******/
CREATE FUNCTION [dbo].[RegexMatches](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatches]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatches.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=61 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

5. Check created objects

   SELECT

        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

List all the objects associated with their assembly name

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class