Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#133 Table Comments

closed
nobody
None
5
2011-05-13
2011-05-11
Derrick Pope
No

I just started using SchemaSpy, it took me a couple of days to figure out how to run the programs but it is working and looks great!!! Thank you for the work you put into this. I am getting comments on my columns but not on my tables. I am using SQL 2005, where do I enter my table comments? Do I use the extended properties and if so what is the name of the property?

Thanks
Derrick

Discussion

  • John Currier
    John Currier
    2011-05-12

    If you're using -t mssql05 then selectTableCommentsSql is:

    SELECT i_s.TABLE_NAME, 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'

    If you're using -t mssql05-jtds or -t mssql05-jtds-instance then selectTableCommentsSql is:

    SELECT OBJECT_NAME(t.object_id) AS TABLE_NAME, ex.value AS comments FROM sys.tables t LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = t.object_id AND ex.name = 'MS_Description' AND minor_id = 0 JOIN sys.schemas s ON t.schema_id = s.schema_id AND s.name = :schema WHERE OBJECTPROPERTY(t.object_id, 'IsMsShipped')=0 ORDER BY OBJECT_NAME(t.object_id)

    Typically the comments would be included in the DDL used to create the table, but I have no clue how SQL Server manages that type of thing.

    John

     
  • Derrick Pope
    Derrick Pope
    2011-05-12

    Ok I have discoverd some additional information. The command being executed for the table comments is wrong. It appears to be truncated. I have tried to set the properties to override the query and it is still executing the wrong query.

    Here is what is being executed.
    SELECT OBJECT_NAME(t.object_id) AS TABLE_NAME, ex.value AS comments FROM sys.tables t

    The properties files has this entry.
    selectTableCommentsSql=SELECT OBJECT_NAME(tb.object_id) AS TABLE_NAME, ex.value AS comments \ FROM sys.tables tb \
    LEFT OUTER JOIN sys.extended_properties ex \ ON ex.major_id = tb.object_id AND ex.name = 'MS_Description' AND minor_id = 0 \ JOIN sys.schemas s ON tb.schema_id = s.schema_id AND s.name = :schema \ WHERE OBJECTPROPERTY(tb.object_id, 'IsMsShipped')=0 \ ORDER BY OBJECT_NAME(tb.object_id)

    The command that is running minus the password is.
    java -jar "C:\Documents and Settings\e0100937\My Documents\My Downloads\Data Dictionary\schemaSpy.jar" -dp "C:\Documents and Settings\e0100937\My Documents\My Downloads\Data Dictionary\jtds\jtds-1.2.5.jar" -t mssql05-jtds -db derrickcu -s dbo -host saltqa -port 1433 -u sa -connprops "C:\Documents and Settings\e0100937\My Documents\My Downloads\Data Dictionary\mssql05-jtds.properties" -css "C:\Documents and Settings\e0100937\My Documents\My Downloads\Data Dictionary\schemaSpy.css" -o "C:\Temp\Graph" -norows -hq

    I thought the properties file would override the for the selectTableCommentsSql but it does not appear to be doing so. Have I missed something to make it use the correct sql statement?

     
  • John Currier
    John Currier
    2011-05-12

    There was a bug in mssql05-jtds.properties where it had a trailing space following the \ on that line (property files are extremely finicky about things like that). That should have been resolved in revision 597 a while ago. Can you try the latest beta at http://schemaspy.sourceforge.net/schemaSpy.jar and see if that resolves it?

    Thanks,
    John

     
  • Derrick Pope
    Derrick Pope
    2011-05-13

    John,

    Thank you, that fixed the problem. I have never used Java so I was not aware they were that finicky. The only reason I was attempting to supply a seperate property file was to resolve the table comment problem, so now I don't need that and I have removed it.

    Thank you for this awesome product. I will make a donnation soon.

     
  • Derrick Pope
    Derrick Pope
    2011-05-13

    Problem resolved with new SchemaSpy.jar.

    removed a trailing space in the properties file.

     
  • Derrick Pope
    Derrick Pope
    2011-05-13

    • status: open --> closed