#146 Invalid object name 'sysproperties'.

open
5
2012-01-27
2012-01-27
Chris Werry
No

Running against SQL Server using jtds get I get the following>
---snip
Using database properties:
[schemaSpy.jar]/net/sourceforge/schemaspy/dbTypes/jtds.properties
JTDS: Setting isolation level to 2
Gathering schema details...............................................................
Failed to retrieve table/view comments: java.sql.SQLException: Invalid object name 'sysproperties'.
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_Descript

Failed to retrieve column comments: java.sql.SQLException: Invalid object name 'sysproperties'.
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_PO
ROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
---snip

select @@version returns:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Discussion

  • devdlee

    devdlee - 2012-08-28

    I was able to pull column comments and table comments after appending 2 properties below to mssql-jtds.properties 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

     
  • devdlee

    devdlee - 2012-08-28

    Updated schemaSpy.jar was tested using
    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) .

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks