1

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.

3

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%'
1
  • Thanks Max, Now I just have to figure out order of precedence. Getting Drop failed because it's a dependent column
    – Tim
    Feb 11 '17 at 0:33
1

Red Gate SQL Dependency Tracker. It will do a diagram: Play around with the different options, but the one that you want is in there.

I don't have access to that tool at my current job (only RedGate Schema Compare and RedGate Data Compare) but we used it at my last job for the purpose of including diagrams in documentation.

0
0

Please do not go through the trouble of copying your database, and dropping all the non-keys! Take a look at SchemaCrawler's diagramming options, in particular, the option to show only significant columns, such as primary and foreign key columns with -command=brief.

Sualeh Fatehi, SchemaCrawler

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.