Support for INFORMATION_SCHEMA

The SQL 2000 and SQL 2005 information schema views are not supported. that is SELECT * FROM info... does not generate any intellisense. For that matter neither does selects on system tables. Is this missing or intentially left out. the information_schema views would be especially helpful.

Not sure what you want to say by that. Do you mean why, SQL Assistant doesn't use the INFORMATION_SCHEMA? Because it is not required and inconsistent in different versions. If you want to use it, you can update the catalog access queries exposed in SQL Assistant options and modify them for your needs.

Hi,

I had this same problem and not only with information_schema views but also another systemobjects.
I've fixed it by changing 2 DB queries (db options/db queries). Note that these are SQL 2005 specific:
Objects (MSSQL):
select
name, type, object_id
from
/*db.*/sys.all_objects
where
type in ('U','S','V','P','X','RF','FN','TF','IF')
and schema_id = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and type = 'P')

Columns (MSSQL):
select
c.name,
t.name +
case
when t.name in ('varchar', 'char', 'nchar', 'nvarchar', 'binary', 'varbinary')
then '(' + cast(c.max_length as varchar(4)) + ')'
when t.name in ('decimal', 'numeric')
then '(' + cast(c.precision as varchar(2)) + ',' + cast(c.scale as varchar(2)) + ')'
else ''
end,
case when c.is_nullable = 1 then 'Y' else 'N' end
from
/*db.*/sys.all_columns c
join /*db.*/sys.types t
on c.user_type_id = t.user_type_id
and c.system_type_id = t.system_type_id
where
c.object_id = :OBJECT_ID
order by
c.column_id

Which SQL Assistant version are you using?

I've checked it on 3.0.31 and 3.5.30

I've checked it on 3.0.31 and 3.5.30

I guess, you installed 3.5 on top of 3.0 and it preserved your old config file because of custom changes made.

Queries in most recent versions are different from what you are referring to.

For example, here is a query for "objects" and it compatible with all versions of MS SQL

	Code:
	select name, xtype, id from /db./dbo.sysobjects where xtype in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS') and ( uid = :SCHEMA_ID or (user_name() = 'dbo' and uid = 4 and id between -299 and -101 and name like 'sys%') ) and not (name = 'dtproperties') and not (name like 'dt%' and xtype = 'P')

and here is "columns"

Code:

select
c.name,
t.name +
case
when t.name in ('varchar', 'char', 'binary', 'varbinary')
   then '(' + case c.length when -1 then 'max' else convert(varchar, c.length) end + ')'
when t.name in ('nvarchar', 'nchar')
   then '(' + case c.length when -1 then 'max' else convert(varchar, c.length / 2) end + ')'
when t.name in ('decimal', 'numeric')
   then '(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) + ')'
else ''
end,
case when c.isnullable = 1 then 'Y' else 'N' end
from
/*db.*/dbo.syscolumns c
join /*db.*/dbo.systypes t
   on c.xtype = t.xtype
   and c.xusertype = t.xusertype
where
c.id = :OBJECT_ID
and c.number = 0
order by
c.colid

Feel free to adjust them as required for your needs.

The thing is that the codes you provided do not allow to query ie. INFORMATION_SCHEMA - and that was whole idea.

Here you go

	Code:
	select name, xtype, id from /db./dbo.sysobjects where xtype in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS') and ( uid = :SCHEMA_ID or (user_name() = 'dbo' and uid = 4 and id between -299 and -101 and name like 'sys%') or (user_name() = 'dbo' and uid = 3) ) and not (name = 'dtproperties') and not (name like 'dt%' and xtype = 'P')

This will work in all versions

well I don't know if your code works in 2000 but it surely does not in 2005, as there are no system objects listed in sysobjects but in sys.all_objects

It works for me. Login as a user mapped to dbo schema and you will see them. In fact you will see both virtual "sys..." synonyms and sys.[objects]

On the other hand, your query cannot be used by all users, because not all have access to sys schema, while most can query catalog tables referenced by sys… virtual synonyms

well.. agreed - it's a specific thing - so I suppose no universal solution.

I agree with you.

On a positive side the catalog queries can be tuned as needed to return best possible results for a specific environment.