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%'
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.