SQL 2000 : Useful TSQL

What is the name of the primary key?

DECLARE @VALUE NVARCHAR(255)
SET @VALUE= (SELECT NAME
               FROM SYSOBJECTS
              WHERE XTYPE = 'PK'
                AND PARENT_OBJ = (OBJECT_ID('MY_TABLE'))
            )
SELECT @VALUE

Check for the existence of column? (With drop statement too!)

IF EXISTS ( SELECT a.name, b.name
                  FROM sysobjects a
                  INNER JOIN syscolumns b
                  ON a.id=b.id
                  WHERE a.xtype='u'
                  AND a.name = 'MY_TABLE'
                  AND b.name= 'MY_COLUMN'
BEGIN
      ALTER TABLE MY_TABLE DROP COLUMN MY_COLUMN
END