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'