Is There A Command That Returns Every Constraint Related To A Colonne/table

Hi,
Not too long ago I was looking to change a primary key in a table from one column to another, the standard accepted procedure to so procedure to do so (I was told) was:
1) drop primary key constraint from old_key_column
2)add primary key constraint to new_key_column
3) drop old_key_column (optional).

In order to carry out the first step, I needed the name of the primary key constraint, at which point I asked around for a command that would return that name (and would quite probably take as a parameter the name of the column that key is related to). The answer was :

Try this:
select name 'constraintName' from sys.indexes where object_id=object_id('<tableName>') and is_primary_key=1

Unfortunately this command never worked for me. Probably because I don't have a table sys.indexes in my DB, but I can see a table sysindexes in the system tables folder, then again that table doesn't have an object_id column, but it has an id column, finally this column only contain obscur numbers.

So I am asking you all (again) for help, do you know about a command that returns every single constraint related to a column (or a table, or both).


One more thing, I am running SQL SERVER 2000, I do know that there are graphic ways to do those very operations (in the enterprise manager or the query analyzer) and I do know those ways (that's how i got past the problem mentioned earlier), I just think that knowing how to do it programmatically would be a plus.

PS: I am a total newbie to T-SQL (and a still a beginner in SQL, so please take it easy with me )