1. Getting all the primary key and foreign keys in a given database

select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, 
CASE WHEN XTYPE ='F' 
THEN 'FORIEGN KEY' 
ELSE 'PRIMARY KEY' END KEY_TYPE , NAME KEY_NAME
from sysobjects 
where Xtype in ('F' , 'pK') 
ORDER BY XTYPE DESC

This script can be used to get all the primary key and foreign key relationships within a given database.It sometimes can be helpful on a project with a complex database having hundreds and thousands of relationships and in such a database if you have to perform some major changes so you can have a quick look and you can know which all tables and relationship will be effected by these changes.

2. Delete All the Stored Procs for a particular database 

Alter Procedure dbo.DeleteAllProcedures
As
      declare @procName varchar(500)
      declare cur cursor
            for select [name] from sys.objects where type = 'p'
      open cur
      fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName <> 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end
      close cur
      deallocate cur
Go

      Grant Execute On dbo.DeleteAllProcedures To Public

Go

This can only be handy when you want to delete all the user defined stored procs for a particular database

3.Change the default owner of a stored proc

USE YourDataBaseName
GO
DECLARE
  @OldOwner sysname,
  @NewOwner sysname

 SET @OldOwner = 'dbo'
 SET @NewOwner = 'dev'

DECLARE CURS CURSOR FOR
SELECT  name
FROM sysobjects
WHERE  type = 'p' 
AND
  uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) 
AND 
  NOT name LIKE 'dt%' FOR READ ONLY  

DECLARE @ProcName sysname

OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@VERSION >= 'Microsoft SQL Server 2005'
    BEGIN
        EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
        exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
    END
    ELSE
        EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')

    FETCH CURS INTO @ProcName
END
CLOSE CURS
DEALLOCATE CURS

4.Finding any stored proc containing the text of your choice

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%yoursearchtext%' 
    AND ROUTINE_TYPE='PROCEDURE'

5.Reducing the size of your transaction log

dbcc shrinkfile (mydatabasename_log, 10);

This command will reduce the size of your database transaction log to 10MB. But if you are on production then you should not use this method to shrink your database, you should avoid shrinking the database without finding the probable reason why is it growing so fast.