I recently wanted to create a document describing the structure of a database automatically, with pretty much these pieces of information. My approach was rather ad hoc, but I created a query using the INFORMATION_SCHEMA views, output that to XML, pushed it through an XSL processor with a stylesheet to generate an HTML file of tables and pasted that into Word. Admittedly, it could have been done (and with more control over the output) by writing some VB to build the Word document, but I wanted some practice with FOR XML and my XSLT was getting rusty.SELECT t.TABLE_NAME, c.ORDINAL_POSITION, c.COLUMN_NAME, c.type, c.notesFROM ( SELECT -- tt is as case here to prevent the optimizer doing something stupid! c.TABLE_NAME, CASE WHEN t.TABLE_TYPE = 'VIEW' THEN 0 ELSE 1 END AS tt, c.ORDINAL_POSITION, c.COLUMN_NAME, COALESCE(DOMAIN_NAME, DATA_TYPE + CASE DATA_TYPE WHEN 'char' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar) + ')' WHEN 'varchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar) + ')' WHEN 'nchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar) + ')' WHEN 'nvarchar' THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar) + ')' WHEN 'numeric' THEN '(' + CAST(NUMERIC_PRECISION AS nvarchar) + ',' + CAST(NUMERIC_SCALE AS nvarchar) + ')' WHEN 'decimal' THEN '(' + CAST(NUMERIC_PRECISION AS nvarchar) + ',' + CAST(NUMERIC_SCALE AS nvarchar) + ')' ELSE '' END) type, CASE WHEN ic.COLUMN_NAME IS NOT NULL THEN 'IDENTITY ' ELSE '' END + CASE WHEN pkc.COLUMN_NAME IS NOT NULL THEN 'pk ' ELSE '' END + CASE WHEN fkc.COLUMN_NAME IS NOT NULL THEN 'FK into ' + pk_table ELSE '' END notes FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME LEFT JOIN ( SELECT OBJECT_NAME(id) AS TABLE_NAME, name AS COLUMN_NAME FROM syscolumns WHERE number = 0 AND status & 0x80 <> 0) ic ON c.TABLE_NAME = ic.TABLE_NAME AND c.COLUMN_NAME = ic.COLUMN_NAME LEFT JOIN ( SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY') pkc ON c.TABLE_NAME = pkc.TABLE_NAME AND c.COLUMN_NAME = pkc.COLUMN_NAME LEFT JOIN ( SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, tc.TABLE_NAME AS pk_table FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON rc.UNIQUE_CONSTRAINT_NAME = tc.CONSTRAINT_NAME) fkc ON c.TABLE_NAME = fkc.TABLE_NAME AND c.COLUMN_NAME = fkc.COLUMN_NAME ) cINNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAMEWHERE t.TABLE_NAME NOT LIKE 'sys%' AND t.TABLE_NAME <> 'dtproperties' AND tt = 1ORDER BY t.TABLE_NAME, ORDINAL_POSITIONFOR XML AUTO The apparently redundant TABLES aliased to t in the outer query is there because otherwise XML AUTO would flatten the structure. Sadly, this caused the query optimizer to go mad and generate a really stupid plan, hence the rather strange tt column.The biggest problem was getting Query Analyser not to chop the lines of output (Tools|Options|Results|Maximum characters per column >= 2034) and removing the line breaks (Emacs 
).Some of the formatting may have been better left to the XSL...The foreign keys are only noted in one direction -- foreign to primary. Since they are shown at the column level, this only works well when an FK is a single column (particularly since it doesn't say which primary key column).The identity columns have to be extracted from sysindexes since the INFORMATION_SCHEMA views don't report this.The query is moderately useful without the FOR XML AUTO.The XSLT looked like this:<?xml version="1.0"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"><!-- Identity rule to copy through description and commentary unchanged --><xsl:template><xsl:copy><xsl:apply-templates select="@*"/><xsl:apply-templates/></xsl:copy></xsl:template><!-- Generate HTML from root node --><xsl:template match="/"><HTML xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <HEAD> <TITLE>UGP Tables</TITLE> </HEAD> <BODY> <xsl:apply-templates select="r"/> </BODY></HTML></xsl:template><xsl:template match="r"> <xsl:apply-templates select="t"/></xsl:template><xsl:template match="t"> <h2><xsl:value-of select="@TABLE_NAME"/></h2> <p>description</p> <table border="1" style="width: 100%"> <xsl:apply-templates select="c"/> </table></xsl:template><xsl:template match="c"> <tr> <td><xsl:value-of select="@COLUMN_NAME"/></td> <td><xsl:value-of select="@type"/></td> <td><xsl:value-of select="@notes"/></td> </tr></xsl:template></xsl:stylesheet>
I've no idea how bad this is, as XSLT goes; it was cobbled together from an old stylesheet I had lying around. It could do with some more style, but the tables fell apart when I tried to paste anything prettier into Word! 
List of all table description
Posted - 2002-04-03 : 03:28:35
Thanks !!!!!VerEdited by - cutever on 04/04/2002 03:13:29
Ver