Drop All Columns in a Table that are not Keys This may seem like an odd request but let me explain, there may be a tool that I haven't found that will do what I need. I couldn't make SSMS do what I needed

Goal: To deliver a Database Diagram that contains tableName, their PKs and FKs for related tables and their relations for several groups of tables.

What I have done is to create a copy of the DB and plan on removing all columns that are not PK or FK from each table. Which I am not thrilled with doing since it could range from 150 tables to 950 tables.

So I would like either (or both :)) the name of a tool that will diagram as I need or a script that I can use to delete all columns that are not PK or FK from all non-system tables.
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%'