#629 Unable to see source of database views

[Peepsalot]

I was having an issue in 2.6.4 where I wanted to see the "source" for a view, but Squirrel is just returning "null". There are many views in this database, and Squirrel will correctly display the source for *most* of them, but for some reason there are a few that just show null. I don't
know what would be special or different about the few that are acting up.

Client: Squirrel SQL client 2.6.4
Database: MS SQL Server 2000
Driver: com.inet.tds.TdsDriver
OS: Linux 2.6.24 (Ubuntu 8.04)

Today I was going to test if this was still a problem in Squirrel 2.6.5a, but the "source" tab does not even appear at all anymore. I can't see any way to view the source for any views with this version.

[Peepsalot]

Logged In: YES
user_id=1041441
Originator: YES

Someone just informed me that the view source can be seen in MS SQL 2000 with the following query:
select syscomments.text FROM syscomments JOIN sysobjects ON syscomments.id = sysobjects.id WHERE sysobjects.name = 'VIEW_NAME_GOES_HERE'

For views with very long source, it will return multiple rows which must then be concatenated. I tested this on the views that are not displaying correctly for me, and they all return multiple rows like this.

So it seems that maybe the code for displaying source does not take into account the possibility of multiple rows?

[Peepsalot]

Logged In: YES
user_id=1041441
Originator: YES

It appears this functionality is provided by the "Microsoft SQL Server Assistant" plugin v 0.3 (internal name "mssql") by Ryan Walberg

This plugin, despite being included in the SQuirreL SQL installer, is not listed on this page:
http://www.squirrelsql.org/index.php?page=plugins

I'm unsure how to contact the author, or even where the source code lies.

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

The MS SQL Sever plugin relies on the following query to show the view definition:

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_CATALOG = ?
AND TABLE_NAME = ?

TABLE_CATALOG will be bound to the value of catalogName in the info tab for the view. TABLE_NAME is bound to the "simpleName" value in the info tab for the view. Can you run this query and check to see that the view_definition is correct? I have setup a local SQLServer 2000 eval edition for testing and it seems to work ok for me. What version of the JDBC driver are you using (getDriverVersion in the Metadata tab for the root node in the object tree)?

Rob

[Rob Manning]

source of test view in SQL-Server 2000

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

The MS SQL Sever plugin relies on the following query to show the view definition:

SELECT VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_CATALOG = ?
AND TABLE_NAME = ?

TABLE_CATALOG will be bound to the value of "catalogName" in the info tab for the view. TABLE_NAME is bound to the "simpleName" value in the info tab for the view. Can you run this query and check to see that the view_definition is correct? I have setup a local SQLServer 2000 eval edition for testing and it seems to work ok for me. What version of the JDBC driver are you using (getDriverVersion in the Metadata tab for the root node in the object tree)? I downloaded I-Net Software's version 7.03 and it seems to show the view source fine.

Rob

File Added: Screenshot.png

[Nobody/Anonymous]

Logged In: NO

Hi Rob,

I just checked, I am using version 7.00.

I also just tested this with the JTDS driver ("jTDS Type 4 JDBC Driver for MS SQL Server and Sybase", v 1.2.2), and got the same problem so I don't think it's an issue in the driver.

I tried manually running the query you gave in Squirrel, and I get null still. I can see that the INFORMATION_SCHEMA.VIEWS is returning a record when I select * from it, it's just that the VIEW_DEFINITION is null.

Again, this problem only happens for particular views in my database, and I think it is only the ones that are very long. This length limit that causes problems appears to be 4096 characters.

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

Oh, 4096 characters you say... My view was not even close to that. I'll see if I can whip up a test view that has > 4096 chars.
Thanks for the info.

Rob

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

I did manage to reproduce this on SQLServer 2000. I opened the management tool and discovered that INFORMATION_SCHEMA.VIEWS is defined as follows:

create view INFORMATION_SCHEMA.VIEWS
as
select
db_name() as TABLE_CATALOG
,user_name(obj.uid) as TABLE_SCHEMA
,obj.name as TABLE_NAME
,case
when exists (select *
from syscomments com3
where com3.id = obj.id
and com3.colid > 1) then convert(nvarchar(4000), NULL)
else com.text
end as VIEW_DEFINITION
,case
when exists (select *
from syscomments com2
where com2.id = obj.id
and CHARINDEX('WITH CHECK OPTION',
upper(com2.text)) > 0) then 'CASCADE'
else 'NONE'
end as CHECK_OPTION
,'NO' as IS_UPDATABLE
from
sysobjects obj
,syscomments com
where
permissions(obj.id) != 0
and obj.xtype = 'V'
and obj.id = com.id
and com.colid = 1

Notice that view_definition has this :

convert(nvarchar(4000), NULL)

My guess is that means that view isn't intended for view definitions with > 4000 chars. Nice!

I guess we'll need to use syscomments directly then.

Rob

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

Fixed in CVS. Adjusted the query for view source to use underlying system tables instead of INFORMATION_SCHEMA.VIEWS, since this view cannot handle views that are greater than 4000 characters. The new query is much like the one that was suggested, only it is ordered:

SELECT text FROM sysobjects o , syscomments c
where o.name = ?
and o.id = c.id
order by c.colid

This will be available in next week's snapshot.

Rob

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

This has been merged to the 2.6 branch and will be available in the 2.6.7 release.

Rob