Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN   
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable 
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable 
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + ' 
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + ' 
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1     
END

SELECT DISTINCT * FROM ##nullable 
WHERE hasNulls = 0  

DROP TABLE ##nullable

You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE

You would have to issue this query

SELECT column_name
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will retrieve every column in the PRIMARY KEY.

You could then make this query generate the ALTER TABLE clauses for you as follows:

SET group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
FROM information_schema.key_column_usage
WHERE table_schema='$db'
AND table_name='$table'
AND constraint_name='PRIMARY';

This will produce a comma-separated list of MODIFY COLUMN clauses you need to apply to the column after dropping the PRIMARY KEY. If you want to drop any of these columns, you have to change MODIFY COLUMN to DROP COLUMN for the columns that need to be dropped.

Give it a Try !!!

UPDATE 2014-12-09 14:00 EST

Your comment

I'm trying to understand how this works. GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL')) is especially confusing to me. Where does column_name come from in this query? I tried running it as is in MySQL, but I get no results

To give you a general idea of how query can help you, here is a table in MySQL on my laptop

mysql> show create table weird.vinner\G
*************************** 1. row ***************************
       Table: vinner
Create Table: CREATE TABLE `vinner` (
  `startnr` int(11) NOT NULL,
  `alder` varchar(25) NOT NULL,
  `kjonn` varchar(25) NOT NULL,
  `ovelseid` varchar(25) NOT NULL,
  `slutttid` int(11) NOT NULL,
  PRIMARY KEY (`startnr`,`slutttid`),
  KEY `slutttid` (`slutttid`),
  CONSTRAINT `vinner_ibfk_1` FOREIGN KEY (`slutttid`) REFERENCES `passering` (`slutttid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

See the PRIMARY KEY. It has two columns: startnr and slutttid.

Now, if I run my code on that tabl, I get this list

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))
    -> FROM information_schema.key_column_usage
    -> WHERE table_schema='weird'
    -> AND table_name='vinner'
    -> AND constraint_name='PRIMARY';
+------------------------------------------------------------------------+
| GROUP_CONCAT(CONCAT('MODIFY COLUMN ',column_name,' DEFAULT NULL'))     |
+------------------------------------------------------------------------+
| MODIFY COLUMN startnr DEFAULT NULL,MODIFY COLUMN slutttid DEFAULT NULL |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

The output creates a MODIFY COLUMN clause for both columns.

If you were dropping the slutttid column, you would use PHP to change MODIFY to DROP

MODIFY COLUMN startnr DEFAULT NULL,DROP COLUMN slutttid

You would be doing this kind of code to generate the clauses needed