SQL User Define Function to Replace String

Create User Define Function that will Replace string from Column. So that Query or Stored Procedure remain neat.

 

 

Eg.

 

 

 

 

 

Create function dbo.ReplaceAddress (@input Varchar(250))

 

Returns Varchar(250)

 

 

As

 

Begin

 

Declare @Address Varchar(250)

Set @Address = @Input

Set @Address = Replace(Replace(Replace(Replace(@Address,' Drive' , ' .Dr'), ' Avenue', ' Ave.'), ' Road' ,' Rd.'), ' Street' , ' St.')

 

Return @Address

 

End

 

Here is Before and After Screenshot using UDF

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Calculated Age Column in SQL Server Using Function

Function to Calculate Age Column based on Floor.

Create FUNCTION [dbo].[fnCalcAge]

(

   @DOB datetime, -- Provide Column name of BirthDate.

   @CurrentDate datetime -- Provide Date or getdate() for Current Date.

 

)

RETURNS real

AS

 

BEGIN

 

    RETURN FLOOR(((DATEDIFF(dd, @DOB, @CurrentDate) +

    CASE WHEN DATEPART(mm, @DOB) = DATEPART(mm, @CurrentDate) AND

    DATEPART(dd, @DOB) = DATEPART(dd, @CurrentDate)

    THEN 1 ELSE 0 END) / 365.25) / .25) * .25

 

END

 

GO

Use function with calculated age column :

For eg. Column name is BirthDate

([dbo].[fnCalcAge]([BirthDate],getdate()))

 

 

Display All Records on Single Page in SSRS

Display all Record in SSRS on single page instead of Multiple Pages.

Before

Change Property of Report

Change Interactive Size Height to 0in

 

 

You might get following error message.

This is due to Performance impact. If  you have huge number of record it is not recommended to change page size and keep it on single page.


After

 

 



 

 



SSRS Expression to change Total Number of Pages on Top

SSRS Expression to show total number of Pages

-- Drag Text Bot in Page Header
--Use following Expression

="Page " & Globals.PageNumber & " of " & Globals.TotalPages

Before Expression

After Expression

List Of Active Trigger in Database

Following Query use to find all Active Trigger in Database

SELECT

                        sysobjects.name AS TriggerName,

                        OBJECT_NAME(parent_obj) AS TableName,

                        OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS UpdateTrigger,

                        OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS DeleteTrigger,

                        OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS InsertTrigger,

                        OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS AfterTrigger,

                        OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS InsteadOfTrigger

 

FROM

                        sysobjects INNER JOIN

                        sysusers

                                    ON sysobjects.uid = sysusers.uid  INNER JOIN

                        sys.tables t

                                    ON sysobjects.parent_obj = t.object_id  INNER JOIN

                        sys.schemas s

                                    ON t.schema_id = s.schema_id

 

WHERE

            sysobjects.type = 'TR'  and

            OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')  <> '1'

 

 

 

 

 

 

List all Default Constraints in Table

Find Default Constraints with Default to in Particular Table in Database.

SELECT

 

    c.name AS ColumnName,

    d.name AS DefaultConstraintName,

    d.definition AS [Default],

            t.name AS TableName

 

FROM

 

             sys.default_constraints d INNER JOIN

             sys.columns c

                         ON d.parent_object_id = c.object_id

                         AND d.parent_column_id = c.column_id INNER JOIN

            sys.tables t

                         ON t.object_id = c.object_id INNER JOIN

            sys.schemas s

                         ON s.schema_id = t.schema_id

 

Where t.Name = 'YourTableName'

 

Find Trigger for Table in SQL Server

Find Trigger for Particular Table in SQL Server

 

 

Select

    so.Name, Text

From

            sysobjects so, syscomments sc

Where

            Type = 'TR'

            and so.id = sc.id

            and Text like '%YourTableName%'