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?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
-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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hello. Could anybody help me with the same problem.
I run the schemaSpy_5.0.0.jar with the next parameters:
C:\Users\Maya>java -jar "C:\Users\Maya\Documents\schemaSpy\schemaSpy_5.0.0.jar" -dp "D:\ Distr\java_libs\jtds-1.3.1-dist\jtds-1.3. 1.jar" -t mssql-jtds -db DW -host SRV-DBMSSQLPRD1 -port 1433 -u rep_user -p * -connprops "C:\Users\Maya\Documents\schemaSpy\schemaSpyGUI20090302\properties\mssql-jtds.properties" -o" C:\Users\Maya\Documents\schemaSpy\schemaSpyGUI20090302\rep_user_DW_ "
Using database properties: [C:\Users\Maya\Documents\schemaSpy\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql-jtds.properties
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_Description'
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_POSITION AND s.name = 'MS_Description' WHERE OBJECTPROPERTY (OBJECT_ID (i_s.TABLE_SCHEMA + '.' + i
_s.TABLE_NAME), 'IsMsShipped') = 0
No tables or views were found in schema 'rep_user'.
The schema does not exist in the database.
Make sure that you specify a valid schema with the -s option and that
the user specified (rep_user) can read from the schema.
Note that schema names are usually case sensitive.
15 schemas exist in this database.
Some of these "schemas" may be users or system schemas.
db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader
db_denydatawriter db_owner db_securityadmin dbo guest imb INFORMATION_SCHEMA sys tmp
These schemas contain tables / views that user 'rep_user' can see:
So the question is why it try to find sql-jtds.properties here: [C:\Users\Maya\Documents\schemaSpy\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql-jtds.properties
?
But the really sql-jtds.properties is here C:\Users\Maya\Documents\schemaSpy\schemaSpyGUI20090302\properties\
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
While this post is old, we still use SchemaSpy here to document our SQL Server schemas, so I thought I'd share the solution for getting this to work on SQL Server 2012 and higher. I found the solution by combining the comments from Derrick and John and researching a bit.
The closest configuration to SQL Server 2012 and higher is mssql05-jtds. That configuration works except for table comments. There's an extra space at the end of one line inside the mssql05-jtds.properties file inside schemaSpy.jar file. So the solution is as follows:
Step 1: Extract the jar file as if it were to be a zip file (a jar file is really just a zip file).
Step 2: Open the following text file for editing (inside the extracted directory): net\sourceforge\schemaspy\dbTypes\mssql05-jtds.properties
Step 3: Find this property in the text editor:
selectTableCommentsSql=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)
Step 4: There is an extra space after the \ on this line:
FROM sys.tables t \
You must delete it. Otherwise, SchemaSpy thinks the query ends on that line.
Step 5: After deleting the extra space, save the file to a permanent directory accessible to SchemaSpy. In this example, I'll use C:\SchemaSpy\mssql05-jtds.properties as the full path of where I saved it.
Step 6: When you run schema spy, instead of using -t mssql05-jtds as the argument, specify the full path like -t C:\SchemaSpy\mssql05-jtds.properties.
After that, it works as expected including the table comments for SQL Server 2012 and above.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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?
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.
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?
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.
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
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
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
I assume you've tried -t mssql05-jtds, right?
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.
-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
Thanks so much for the detailed information. Changing to mssql05-jtds and removing the -connprops did the trick. Wonderful tool.
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
Hello. Could anybody help me with the same problem.
I run the schemaSpy_5.0.0.jar with the next parameters:
C:\Users\Maya>java -jar "C:\Users\Maya\Documents\schemaSpy\schemaSpy_5.0.0.jar" -dp "D:\ Distr\java_libs\jtds-1.3.1-dist\jtds-1.3. 1.jar" -t mssql-jtds -db DW -host SRV-DBMSSQLPRD1 -port 1433 -u rep_user -p * -connprops "C:\Users\Maya\Documents\schemaSpy\schemaSpyGUI20090302\properties\mssql-jtds.properties" -o" C:\Users\Maya\Documents\schemaSpy\schemaSpyGUI20090302\rep_user_DW_ "
Using database properties:
[C:\Users\Maya\Documents\schemaSpy\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql-jtds.properties
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_Description'
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_POSITION AND s.name = 'MS_Description' WHERE OBJECTPROPERTY (OBJECT_ID (i_s.TABLE_SCHEMA + '.' + i
_s.TABLE_NAME), 'IsMsShipped') = 0
No tables or views were found in schema 'rep_user'.
The schema does not exist in the database.
Make sure that you specify a valid schema with the -s option and that
the user specified (rep_user) can read from the schema.
Note that schema names are usually case sensitive.
15 schemas exist in this database.
Some of these "schemas" may be users or system schemas.
db_accessadmin db_backupoperator db_datareader db_datawriter db_ddladmin db_denydatareader
db_denydatawriter db_owner db_securityadmin dbo guest imb INFORMATION_SCHEMA sys tmp
These schemas contain tables / views that user 'rep_user' can see:
INFORMATION_SCHEMA dbo imb sys
My mssql-jtds.properties is here http://pastebin.com/F4shxWg9
So the question is why it try to find sql-jtds.properties here:
[C:\Users\Maya\Documents\schemaSpy\schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mssql-jtds.properties
?
But the really sql-jtds.properties is here C:\Users\Maya\Documents\schemaSpy\schemaSpyGUI20090302\properties\
While this post is old, we still use SchemaSpy here to document our SQL Server schemas, so I thought I'd share the solution for getting this to work on SQL Server 2012 and higher. I found the solution by combining the comments from Derrick and John and researching a bit.
The closest configuration to SQL Server 2012 and higher is mssql05-jtds. That configuration works except for table comments. There's an extra space at the end of one line inside the mssql05-jtds.properties file inside schemaSpy.jar file. So the solution is as follows:
Step 1: Extract the jar file as if it were to be a zip file (a jar file is really just a zip file).
Step 2: Open the following text file for editing (inside the extracted directory): net\sourceforge\schemaspy\dbTypes\mssql05-jtds.properties
Step 3: Find this property in the text editor:
selectTableCommentsSql=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)
Step 4: There is an extra space after the \ on this line:
FROM sys.tables t \ You must delete it. Otherwise, SchemaSpy thinks the query ends on that line.
Step 5: After deleting the extra space, save the file to a permanent directory accessible to SchemaSpy. In this example, I'll use C:\SchemaSpy\mssql05-jtds.properties as the full path of where I saved it.
Step 6: When you run schema spy, instead of using -t mssql05-jtds as the argument, specify the full path like -t C:\SchemaSpy\mssql05-jtds.properties.
After that, it works as expected including the table comments for SQL Server 2012 and above.