Ich habe die dankbare Aufgabe SQL Tabellen zu dokumentieren. Das soll ich in Word machen. Ich suche nun ein Tool womit ich diese Aufgabe einfach erledigen kann. Ich will einfach nicht jedes Feld in einer Tabelle im Word erfassen müssen. Die Struktur wäre einfach. Name/Type/Beschreibung in Tabellen Form.
Ich habe mal vor einiger Zeit mal direkt in SQL was geschrieben, evtl. ist es für dich nützlich. Müsstest vielleicht 1-2 Sachen für deine Zwecke anpassen, einfach in Management Studio ausführen.
USE [AdventureWorks]; SET NOCOUNT ON
DECLARE @tableName AS NVARCHAR(128)
PRINT 'Database ' + db_name() PRINT 'Document created at ' + Convert(char(10), getdate(), 103) PRINT ''
DECLARE tables_cursor CURSOR FOR SELECT [name] FROM sys.all_objects WHERE type_desc = 'USER_TABLE' OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tableName WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'Table: ' + @tableName
SELECT DISTINCT c.name AS [Column],ISC.data_type AS [Type],c.max_length AS [Length], CASE ISC.is_nullable WHEN 'NO' THEN 'YES' ELSE 'NO' END AS [IsNullable], isnull(ISC.column_default,'') AS [Default value], CASE c.is_identity WHEN '0' THEN '' ELSE 'YES' END AS [Auto increment], ISNULL(CASE WHEN i.is_primary_key = 1 THEN 'Primary key ' WHEN i.is_unique = 1 THEN 'Unique key ' ELSE 'Key with duplicates ' END + 'Type '+ i.type_desc,'') AS [Key], CASE WHEN isnull(fc.name,'0') = '0' THEN '' ELSE 'Foreign key : ' + isnull(allObj.name,'') + '.' + isnull(allCols.name,'') END AS [Constraint], isnull(ep.value,'') as description FROM sys.all_objects o INNER JOIN sys.all_columns c ON o.object_id = c.object_id LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS ISC ON ISC.table_name = o.name AND c.name = ISC.column_name LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON ccu.table_name = o.name AND c.name = ccu.column_name LEFT OUTER JOIN sys.foreign_keys fc on fc.name = ccu.constraint_name LEFT OUTER JOIN sys.all_columns allCols ON allCols.object_id = fc.referenced_object_id AND fc.key_index_id = allCols.column_id LEFT OUTER JOIN sys.all_objects allObj ON allObj.object_id = allCols.object_id LEFT OUTER JOIN sys.index_columns idxCols ON o.object_id = idxCols.object_id AND idxCols.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON i.object_id = idxCols.object_id AND idxCols.index_id = i .index_id left outer join (select * from sys.extended_properties where name = 'MS_Description') ep on o.object_id = ep.major_id and c.column_id = ep.minor_id WHERE o.name = @tableName
Ein fertiges Tool kenn ich nicht, aber ich mach das immer so:
Tabellenweise: 1) HeidiSql 2) DB Verbinden 3) SHOW COLUMNS FROM <table>; 4) Menu -> Export -> CSV/HTML/wasauchimmer 5) Import in Word
Oder komplett: 1) MySQL Workbench 2) Menu -> Database -> Reverse Engineer... 3) Wizard folgen 4) Menu -> View -> Advanced -> Output 5) Menu -> Plugins -> Catalog -> Dump all table columns 6) In Word und umformatieren