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

SQL Server 2008 table and column comments

Help
akabak
2011-05-15
2013-05-29
  • akabak
    akabak
    2011-05-15

    I've got schemaspy working against my SQL Server 2008 database, but I can't get it to generate the table and column comments into the html pages. Using the mssql-jtds database type was the only way I could get the tool to work properly, and I added the following lines in my mssql-jtds.properties file:

    selectTableCommentsSql=select sys.tables.name as table_name, (select value from sys.extended_properties where sys.extended_properties.major_id = sys.tables.object_id and sys.extended_properties.minor_id = 0) as comments from sys.tables where sys.tables.schema_id=1
    selectColumnCommentsSql=select sys.tables.name as table_name, sys.columns.name as column_name, (select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id and sys.extended_properties.minor_id = sys.columns.column_id) as comments from sys.columns, sys.tables, sys.types, sys.schemas where sys.columns.object_id = sys.tables.object_id and sys.columns.user_type_id=sys.types.user_type_id and sys.tables.schema_id=sys.schemas.schema_id and sys.schemas.name = 'dbo'

    I have run these queries against my database, and they return the correct results. When I looked at the mssql.properties file, I saw a column comments query already in it, but the tool doesn't work correctly when I use the mssql dbtype parameter as it fails to load the jdbc driver for sql server 2000. I tried downloading Microsoft SQL Server JDBC Driver 3.0 and pointing to various .jar files in that package via the -dp parameter, but it doesn't work either.

    Any ideas how to get the selectTableCommentsSql and select ColumnCommentsSql with mssql-jtds or to get mssql to work with sql server 2008?

     
  • Derrick Pope
    Derrick Pope
    2011-05-16

    I had a similar problem with SQL 2005.  I actually downloaded the jtds-1.2.5.jar file for the database properties from http://jtds.sourceforge.net/index.html.  This resolved most of my problems except for the table comments.  For that fix I got a new SchemaSpy.jar file from John.  Apparently there was a trailing space in the mssql05-jtds.properties file. 

    Perhaps this information will help.

     
  • akabak
    akabak
    2011-05-17

    I think I've narrowed down what the issue is. Despite specifying the .properties file I want SchemaSpy to use, it is using a different .properties file. When using this command:

    java -jar "C:\Third Party Code\SchemaSpy\schemaSpy.jar" -dp "C:\Third Party Code\SchemaSpy\jtds-1.2.5.jar" -t mssql-jtds -db scout -s dbo -host scout -port 1433 -u sa -connprops "C:\Third Party Code\SchemaSpy\properties\mssql-jtds.properties" -o "C:\Scout\Database\doc" -norows  -hq  -nologo

    I get this output:

    Using database properties:
      /net/sourceforge/schemaspy/dbTypes/mssql-jtds.properties

    Gathering schema details……………………
    20:37:13.148 WARNING: Database.warning - Failed to retrieve table/view comments: java.sql.SQLException: Invalid object name 'sysproperties'.
    20:37:13.234 WARNING: Database.warning - SELECT i_s.TABLE_NAME, s.value AS comments FROM INFORMATION_SCHEMA.TABLES i_s INNER JOIN sysproperties s ON s.id = OBJECT_ID(i_s.table_catalog + '..' + i_s.table_name) WHERE s.type = 3 AND s.name = 'MS_Description'

    20:37:13.242 WARNING: Database.warning - Failed to retrieve column comments: java.sql.SQLException: Invalid object name 'sysproperties'.

    20:37:13.243 WARNING: Database.warning - SELECT i_s.TABLE_NAME, i_s.COLUMN_NAME, s.value AS comments FROM INFORMATION_SCHEMA.COLUMNS i_s INNER JOIN sysproperties s ON s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME) AND s.smallid = i_s.ORDINAL_POSITION AND s.name = 'MS_Description' WHERE OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0

    These are not the same queries that are in the mssql-jtds.properties file I specify at the command line. Any idea where to find the file it's using?

     
  • Derrick Pope
    Derrick Pope
    2011-05-17

    I did notice that as well.  When I received the new SchemaSpay.jar file from John I was able to get my comments.  I will need to override those properties against another database so I will be in a similar situation soon.  Perhaps John can give us some direction on why this is not working and how to get it to work correctly.

     
  • John Currier
    John Currier
    2011-05-17

    mssql-jtds.properties has a line extends=mssql, so it's "inheriting" any SQL from that file if it hasn't been overwritten locally. 

    There are two flavors of the JTDS configuration files.  One (mssql-jtds) that uses the default MS SQL queries from mssql.properties and one (mssql05-jtds extends mssql-jtds) that uses customized queries.  Use the one that works for your environment.

    John

     
  • akabak
    akabak
    2011-05-18

    I've updated all mssql*.properties files so that none of them references sysproperties, yet the error I get in the output is still Invalid object name 'sysproperties'. The only db type I can get to work against my SQL Server 2008 database is mssql-jtds, but somehow schema spy is NOT using my local .properties file, any of them. I don't understand where the following line in the output comes from:

    /net/sourceforge/schemaspy/dbTypes/mssql-jtds.properties

    why is sourceforge referenced? Why is it finding a query referencing sysproperties when none of my .properties files has that term in it?

    I really appreciate this tool and would like it to meet all of my needs. Any information would be appreciated. Thanks.

    amanda

     
  • John Currier
    John Currier
    2011-05-18

    The algorithm attempts to resolve the specified .properties file in the following order:

    1: Assumes it's a file name and attempts to open it from the file system.  It assumes you're giving it an appropriate relative or absolute path (e.g. -t ./mydb.properties).
    2: Repeats 1: after appending ".properties" to the specified name (e.g. -t ./mydb)
    3: Assumes it's the fully-qualified name of a resource in the classpath (the jar in this case) and attempts to load it as a ResourceBundle.
    4: Repeats 3: explicitly specifying the path to the dbTypes directory.

    In your case it's not finding a mssql-jtds file nor a mssql-jtds.properties file in the directory you specified (current), so everything is failing until 4:.

    Hope this helps explain it,
    John

     
  • John Currier
    John Currier
    2011-05-18

    I assume you've tried -t mssql05-jtds, right?

     
  • akabak
    akabak
    2011-05-18

    I've tried mssql05-jtds, yes. It gives me an error about jdbs. Perhaps I just need to download the right drivers. I'm a .net person, and am not entirely comfortable with the dependencies for this tool.

    But I'm not sure why step 1 in your response is failing for me. I'm passing in the absolute path to the .properties file as you can see in this command line string:

    java -jar "C:\Third Party Code\SchemaSpy\schemaSpy.jar" -dp "C:\Third Party Code\SchemaSpy\jtds-1.2.5.jar" -t mssql-jtds -db scout -s dbo -host scout -port 1433 -u sa -connprops "C:\Third Party Code\SchemaSpy\properties\mssql-jtds.properties" -o "C:\Scout\Database\doc" -norows  -hq  -nologo

    In your example, you seem to be specifying the properties file with the -t switch, which I thought was just the dbtype. I've been using the -connprops switch to specify the properties file path. Perhaps I'll remove the -connprops switch entirely and let schemaspy try to use step 2 and append .properties to mssql-jtds and find the file that way.

    I will let you know what I find. Thanks for your help so far.

    At first, I'd built this string by hand but then I used the GUI tool to build it, and it came out the same way. In the GUI tool, I browsed directly to the .properties file, then checked the path, and it is correct.

     
  • John Currier
    John Currier
    2011-05-18

    -connprops is a mechanism to specify custom connection properties that are specific to how you connect to your database and not generically to that type of database (as specified with -t).  This collection of key=value pairs is the mechanism that some databases use to do custom (or required) configuration of the specific database connection.  jTDS uses a ton of optional properties that I could have included in mssql-jtds.properties' connectionSpec, but then they'd be required for everyone that wanted to use that file.

    In summary: -t specifies details about the type of database and is generally reusable by anyone using that type of database.  -connprops allows for customization of a specific connection to a database and is generally not reusable since it would contain user- and/or database-specific details.  It's rare that a database would require you to use -connprops.  Unfortunately jTDS does.

    In your case I assume you'd use -t mssql05-jtds and use the inline version of -connprops.  E.g. -connprops=domain\=mydomain.  If you require more elaborate connection configuration options (as specified in the previously linked jTDS FAQ) then put them in their own file.

    Hope this helps,
    John

     
  • akabak
    akabak
    2011-05-19

    Thanks so much for the detailed information. Changing to mssql05-jtds and removing the -connprops did the trick. Wonderful tool.

     
  • devdlee
    devdlee
    2012-08-28

    To fix an issue with an message below with SQL Server 2008,
    Failed to retrieve table/view comments: java.sql.SQLException: Invalid object name 'sysproperties'.

    I was able to pull tables comments and column comments after
    I updated mssql-jtds.properties with 2 properties below in schemaSpy.jar

    selectColumnCommentsSql=SELECT OBJECT_NAME(EXP.major_id) AS TABLE_NAME,  C.name AS COLUMN_NAME, EXP.value AS comments \
    FROM sys.extended_properties AS EXP \
    LEFT OUTER JOIN sys.columns AS C \
    ON C.object_id = EXP.major_id \
    AND C.column_id = EXP.minor_id \
    WHERE \
    EXP.class_desc = 'OBJECT_OR_COLUMN' and \
    C.name is not null

    selectTableCommentsSql=SELECT OBJECT_NAME(EXP.major_id) AS TABLE_NAME, EXP.value AS comments \
    FROM sys.extended_properties AS EXP \
    LEFT OUTER JOIN sys.columns AS C \
    ON C.object_id = EXP.major_id \
    AND C.column_id = EXP.minor_id \
    WHERE \
    EXP.class_desc = 'OBJECT_OR_COLUMN' and \
    EXP.minor_id = 0