https://www.insidesql.org/blogs/frankkalis/?disp=comments de-DE http://backend.userland.com/rss 60 Thu, 22 Dec 2011 13:06:28 +0000 admin c1814@https://www.insidesql.org/blogs/ Hi Jürgen, danke! Genau das war die Intention. Es sollte nichts grossartiges sein. Nur für schnelle Ad-Hoc Zwecke, was man halt immer wieder braucht, aber nie findet, wenn man es braucht. :-) Ich baue das in den Download zusammen mit Christoph's Vorschlag ein. https://www.insidesql.org/blogs/frankkalis/2011/12/09/quick-and-dirty-datenbank-dokumentierer#c1814 Thu, 22 Dec 2011 08:38:53 +0000 Jürgen c1813@https://www.insidesql.org/blogs/ Hi. Bin über Dein Statement gestolpert und dacht, das kann man ja immer brauchen ;-) Hier noch ein paar kleine Optimierungen (Is_Index, Schema, Comment der Spalte) SELECT TOP (100) PERCENT DB_NAME() AS [Database], sys.schemas.name AS [Schema], O.name AS Table_Name, O.modify_date, C.name AS Column_Name, C.column_id AS Column_id, P.value AS Column_Comment, S.name AS DataType, C.max_length, C.is_nullable, C.is_identity, C.collation_name, CASE WHEN I.index_id > 0 THEN 1 ELSE 0 END AS Column_is_Index FROM sys.objects AS O INNER JOIN sys.columns AS C ON O.object_id = C.object_id INNER JOIN sys.systypes AS S ON C.system_type_id = S.xtype INNER JOIN sys.schemas ON O.schema_id = sys.schemas.schema_id LEFT OUTER JOIN sys.index_columns AS I ON C.object_id = I.object_id AND C.column_id = I.column_id LEFT OUTER JOIN sys.extended_properties AS P ON C.column_id = P.minor_id AND C.object_id = P.major_id WHERE (O.type = 'U') AND (O.is_ms_shipped = 0) ORDER BY Table_Name, Column_id Gruß Jürgen 0 THEN 1 ELSE 0 END AS Column_is_Index FROM sys.objects AS O INNER JOIN sys.columns AS C ON O.object_id = C.object_id INNER JOIN sys.systypes AS S ON C.system_type_id = S.xtype INNER JOIN sys.schemas ON O.schema_id = sys.schemas.schema_id LEFT OUTER JOIN sys.index_columns AS I ON C.object_id = I.object_id AND C.column_id = I.column_id LEFT OUTER JOIN sys.extended_properties AS P ON C.column_id = P.minor_id AND C.object_id = P.major_id WHERE (O.type = 'U') AND (O.is_ms_shipped = 0) ORDER BY Table_Name, Column_id Gruß Jürgen]]> https://www.insidesql.org/blogs/frankkalis/2011/12/09/quick-and-dirty-datenbank-dokumentierer#c1813 Mon, 12 Dec 2011 07:46:11 +0000 admin c1778@https://www.insidesql.org/blogs/ Hi Christoph, wahrscheinlich. Die Abfrage habe ich in 1-2 Minuten geschrieben. Den grössten Teil der Zeit hat die Schleife beansprucht. https://www.insidesql.org/blogs/frankkalis/2011/12/09/quick-and-dirty-datenbank-dokumentierer#c1778 Fri, 09 Dec 2011 09:13:20 +0000 cmu c1776@https://www.insidesql.org/blogs/ Hi Frank, müßte es nicht c.max_length heißen, damit auch wirklich die Länge der Spalte angezeigt wird? Was hältst Du von der Verwendung der information_schemas? SELECT DB_NAME() as CurrentDatabase, O.TABLE_SCHEMA, O.TABLE_NAME, C.COLUMN_NAME, C.ORDINAL_POSITION, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION, C.NUMERIC_SCALE, C.is_nullable FROM information_schema.tables O JOIN INFORMATION_SCHEMA.columns C ON O.TABLE_CATALOG = C.TABLE_CATALOG and o.TABLE_SCHEMA = c.TABLE_SCHEMA and o.TABLE_NAME = c.TABLE_NAME ORDER BY O.TABLE_SCHEMA, O.TABLE_NAME, C.COLUMN_NAME; https://www.insidesql.org/blogs/frankkalis/2011/12/09/quick-and-dirty-datenbank-dokumentierer#c1776