Important and useful command in SQL server 2005/2008

How to get all trigger definitions in database?

            SELECT   sysobjects.name AS [Trigger Name],

            SUBSTRING(syscomments.text, 0, 1000) AS [Trigger Definition],

            OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],

            syscomments.encrypted AS [IsEncrpted] FROM sysobjects 

            INNER JOIN syscomments ON sysobjects.id = syscomments.id WHERE   

            (sysobjects.xtype = 'TR')

add user to sql server admin role 

sp_addsrvrolemember 'domain\username', 'sysadmin'

Count all system objects

select count(*) as [Table] from sys.objects where type ='U'

select count(*) as [Function] from sys.objects where type ='FN'

select count(*) as [View] from sys.objects where type ='V'

select count(*) as [SP] from sys.objects where type ='P'

select count(*) as [Triggers] from sys.objects where type ='TR'

select count(*) as [Contraints] from sys.objects where type ='PK'

list all tables referred by a stored procedure

SELECT o.name, t.TABLE_NAME, c.text
  FROM syscomments c
  JOIN sysobjects o
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%'+t.TABLE_NAME+'%' 

list all triggers in database

SELECT 

    [Table] = OBJECT_NAME(o.parent_obj), 

    [Trigger] = o.[name], 

    [Type] = CASE WHEN 

        ( 

        SELECT 

            cmptlevel 

        FROM 

            master.dbo.sysdatabases 

        WHERE 

            [name] = DB_NAME() 

        ) = 80 THEN 

        CASE WHEN 

            OBJECTPROPERTY(o.[id], 

            'ExecIsInsteadOfTrigger') = 1 THEN 

                'Instead Of' 

            ELSE 

                'After' 

            END 

        ELSE 

            'After' 

        END, 

    [Insert] = CASE WHEN 

        OBJECTPROPERTY(o.[id], 

        'ExecIsInsertTrigger') = 1 THEN 

            'Yes' 

        ELSE 

            'No' 

        END, 

    [Update] = CASE WHEN 

        OBJECTPROPERTY(o.[id], 

        'ExecIsUpdateTrigger') = 1 THEN 

            'Yes' 

        ELSE 

            'No' 

        END, 

    [Delete] = CASE WHEN  

        OBJECTPROPERTY(o.[id], 

        'ExecIsDeleteTrigger') = 1 THEN 

            'Yes' 

        ELSE 

            'No' 

        END, 

    [Enabled?] = CASE WHEN 

        OBJECTPROPERTY(o.[id], 

        'ExecIsTriggerDisabled') = 0 THEN 

            'Enabled' 

        ELSE 

            'Disabled' 

        END 

FROM 

    sysobjects o 

WHERE 

    OBJECTPROPERTY(o.[id], 'IsTrigger') = 1 

    -- leave out the following clause if you need to 

    -- include system triggers, e.g. those in MSDB 

    AND 

    OBJECTPROPERTY(o.[id], 'IsMSShipped') = 0 

ORDER BY 

    1,2