#12 Using SchemaSpy with SqlServer 2008 + sqljdbc4.jar

[Anonymous]

Using cygwin + SqlServer 2008 + sqljdbc4.jar can throw an error, caused by the variant type of some queries defined in mssql05.properties.

If you need to use sqljdbc4.jar with SqlServer2008, remember to cast these columns, as follows:

# return table_name, comments for current schema
# SQL provided by Stefano Santoro
selectTableCommentsSql=SELECT CONVERT(varchar(200),i_s.TABLE_NAME) as TABLE_NAME, CONVERT(varchar(200),s.value) AS comments FROM INFORMATION_SCHEMA.Tables i_s \ INNER JOIN sys.extended_properties s ON s.major_id = OBJECT_ID(i_s.table_catalog + '..' + i_s.table_name) \ WHERE s.class = 1 AND s.name = 'MS_Description'

# reference: http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-description-property-of-a-column.html
# return table_name, column_name, comments for a specific :schema
# SQL provided by Ernest Zapata
selectColumnCommentsSql=SELECT CONVERT(varchar(200),OBJECT_NAME(c.object_id)) AS TABLE_NAME, CONVERT(varchar(200),c.name) AS COLUMN_NAME, CONVERT(varchar(200),ex.value) AS comments FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 ORDER BY OBJECT_NAME(c.object_id), c.column_id