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%'

 

 

Find Primary Key, Foreign Key, Constrain in SQL Table

Find Column Name, Primary Key, Foreign Key, Constrain for particular Table in Database.

 

 

SELECT distinct

                        Col.Column_Name,

                        Col.CONSTRAINT_Name as [Key] ,

                        CONSTRAINT_TYPE as Constraint_Type 

From

    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,

    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col

WHERE

    Col.Constraint_Name = Tab.Constraint_Name

    AND Col.Table_Name = Tab.Table_Name

    AND Col.Table_Name = 'YourTableName'

 

Order By in SQL Server with Condition or Clause

When you order by column in table in SQL Server but with the clause here is the example :

Suppose we have StateCity table.

 

 

 

Now we want to in a order that State NY is first and Else in Alphabetic Order here is the Query :

 

 

Select * from StateCity

Order By Case When [State]  = 'NY' Then '1'

              When [State]  = 'NJ' Then '2'

                       Else [State]  END ASC

 

Result:

 

 

 

Suppose you want to Order By Particular State and Particular City here is the example of the Query :

 

Select * from StateCity

Order By Case

                       When [State]  = 'NY' Then '1'

                       When [State]  = 'NJ' Then '2'

         

                                Else [State]  END ASC,

                       Case

                       When [City] = 'New York' Then '1'

                       When [City] = 'Atlanta' Then '1'

                       When [City] = 'Parsippany' Then '1'

 

                                 Else [City] END ASC

 

 

 Result: