SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Support for INFORMATION_SCHEMA

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Support for INFORMATION_SCHEMA
Author Message
fhanlon



Joined: 06 Dec 2006
Posts: 5

Post Support for INFORMATION_SCHEMA Reply with quote
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.
Fri Apr 20, 2007 4:32 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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.
Fri Apr 20, 2007 5:53 pm View user's profile Send private message
tarik



Joined: 26 Nov 2007
Posts: 7

Post Reply with quote
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
Wed Aug 27, 2008 9:44 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
Which SQL Assistant version are you using?
Wed Aug 27, 2008 10:01 am View user's profile Send private message
tarik



Joined: 26 Nov 2007
Posts: 7

Post Reply with quote
I've checked it on 3.0.31 and 3.5.30
Wed Aug 27, 2008 10:15 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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.
Wed Aug 27, 2008 10:24 am View user's profile Send private message
tarik



Joined: 26 Nov 2007
Posts: 7

Post Reply with quote
The thing is that the codes you provided do not allow to query ie. INFORMATION_SCHEMA - and that was whole idea.
Wed Aug 27, 2008 10:30 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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
Wed Aug 27, 2008 10:38 am View user's profile Send private message
tarik



Joined: 26 Nov 2007
Posts: 7

Post Reply with quote
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
Wed Aug 27, 2008 10:55 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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
Wed Aug 27, 2008 11:02 am View user's profile Send private message
tarik



Joined: 26 Nov 2007
Posts: 7

Post Reply with quote
well.. agreed - it's a specific thing - so I suppose no universal solution.
Wed Aug 27, 2008 11:37 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6485

Post Reply with quote
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.
Wed Aug 27, 2008 11:48 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.