This T-SQL will generate a series of ALTER TABLE statements that you can copy-and-paste into a query window in SSMS to remove columns that are neither primary keys nor foreign keys.
;WITH ForeignKeys AS (
SELECT t.object_id
, c.column_id
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.parent_object_id = fkc.parent_object_id
INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
), PrimaryKeys AS (
SELECT t.object_id
, c.column_id
FROM sys.key_constraints kc
INNER JOIN sys.indexes i ON kc.parent_object_id = i.object_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.index_column_id = c.column_id
)
SELECT 'ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
DROP COLUMN ' + QUOTENAME(c.name) + ';'
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE NOT EXISTS (
SELECT 1
FROM ForeignKeys fk
WHERE fk.object_id = t.object_id
AND fk.column_id = c.column_id
)
AND NOT EXISTS (
SELECT 1
FROM PrimaryKeys pk
WHERE pk.object_id = t.object_id
AND pk.column_id = c.column_id
)
It will correctly handle tables in various schemas, as well.
You may also have to first remove any default constraints:
SELECT N'alter table ' + QUOTENAME(t.name) +
' drop constraint [' + OBJECT_NAME(so.object_id) + N']'
FROM sys.objects so
INNER JOIN sys.tables t
ON t.object_id = so.parent_object_id
WHERE OBJECT_NAME(so.OBJECT_ID) like 'DF%'
