SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Cross Schema Search

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Cross Schema Search
Author Message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Cross Schema Search Reply with quote
I'd like to be able to start a Select as:

Code:
Select * from

CTRL+SPACE would show me: schemas, databases, and tables/views/etc within this database, across all schemas since a schema is not provided. I have the query working for this.

However, when I select the table, it gives it fully qualified with dbo schema, which is not the correct schema.
Thu Aug 25, 2016 3:46 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
I'm not able to reproduce that. Can you please describe your environment? What's the state of Always Fully Qualify Object Names option in SQL Assistant settings for SQL. Server?
Thu Aug 25, 2016 3:51 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
You probably need the query in order to get across all schemas. My setting is With schema name. Running 7.5.502 Pro with SQL 2014. I have no objects in dbo, all are within other schemas in this database.

Code:

if @@version like '%SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%SQL Server 2014%'
or @@version like '%SQL Azure%'

  select
     name, CASE WHEN type = 'SN' THEN '^' +
       CASE CAST(OBJECTPROPERTYEX(object_id,'BaseType') AS VARCHAR)
       WHEN 'AF' THEN 'F'
       WHEN 'FN' THEN 'F'
       WHEN 'FS' THEN 'F'
       WHEN 'FT' THEN 'U'
       WHEN 'IF' THEN 'U'
       WHEN 'IT' THEN 'T'
       WHEN 'P' THEN 'P'
       WHEN 'PC' THEN 'P'
       WHEN 'S' THEN 'T'
       WHEN 'SQ' THEN 'I'
       WHEN 'TF' THEN 'U'
       WHEN 'TT' THEN 'K'
       WHEN 'U' THEN 'T'
       WHEN 'V' THEN 'V'
       WHEN 'X' THEN 'P'
       ELSE ' '
       END
     ELSE type END,
     object_id, create_date, modify_date
  from
     [$DB_NAME$].sys.all_objects
  where
     type in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS','TR','SO')
     and (
           schema_id = :SCHEMA_ID
           or (
               1 = :SCHEMA_ID
               and user_name() = 'dbo'
               and schema_id = 4
               and name in ('sysobjects','sysindexes','syscolumns','systypes','syscomments','sysfiles1','syspermissions','sysusers','sysproperties','sysdepends','sysreferences','sysfulltextcatalogs','sysindexkeys','sysforeignkeys','sysmembers','sysprotects','sysfulltextnotify','sysfiles','sysfilegroups')
              )
                      OR
           (:SCHEMA_ID IS NULL)
         or (:SCHEMA_ID =1 and user_name() <> 'dbo' and schema_id <> 4)

         )
     and not (name = 'dtproperties')
     and not (name like 'dt%' and type = 'P')

  union all

  SELECT t.NAME + COALESCE(CHAR(0) + t1.name
      + case
      when t1.name in ('varchar', 'char', 'binary', 'varbinary')
        then '(' + case t1.max_length when -1 then 'max' else convert(varchar, t1.max_length) end + ')'
      when t1.name in ('nvarchar', 'nchar')
        then '(' + case t1.max_length when -1 then 'max' else convert(varchar, t1.max_length / 2) end + ')'
      when t1.name in ('decimal', 'numeric')
        then '(' + convert(varchar, t1.[precision]) + ',' + convert(varchar, t1.scale) + ')'
      else ''
      END, ''),
      COALESCE(o.type, 'TD'),
      COALESCE(tt.type_table_object_id, t.user_type_id),
      o.create_date, o.modify_date
  FROM [$DB_NAME$].sys.types t
  LEFT JOIN [$DB_NAME$].sys.types t1 ON t.system_type_id = t1.user_type_id
  LEFT JOIN [$DB_NAME$].sys.table_types tt ON tt.name = t.name
  LEFT JOIN [$DB_NAME$].sys.objects o ON tt.type_table_object_id = o.[object_id]
  WHERE t.is_user_defined = 1 and t.schema_id = :SCHEMA_ID

else
if @@version like '%SQL Server 2005%'

  select
     name, CASE WHEN type = 'SN' THEN '^' +
       CASE CAST(OBJECTPROPERTYEX(object_id,'BaseType') AS VARCHAR)
       WHEN 'AF' THEN 'F'
       WHEN 'FN' THEN 'F'
       WHEN 'FS' THEN 'F'
       WHEN 'FT' THEN 'U'
       WHEN 'IF' THEN 'U'
       WHEN 'IT' THEN 'T'
       WHEN 'P' THEN 'P'
       WHEN 'PC' THEN 'P'
       WHEN 'S' THEN 'T'
       WHEN 'SQ' THEN 'I'
       WHEN 'TF' THEN 'U'
       WHEN 'TT' THEN 'K'
       WHEN 'U' THEN 'T'
       WHEN 'V' THEN 'V'
       WHEN 'X' THEN 'P'
       ELSE ' '
       END
     ELSE type END,
     object_id, create_date, modify_date
  from
     [$DB_NAME$].sys.all_objects
  where
     type in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS', 'TR')
     and (
           schema_id = :SCHEMA_ID
           or (
               1 = :SCHEMA_ID
               and user_name() = 'dbo'
               and schema_id = 4
               and name in ('sysobjects','sysindexes','syscolumns','systypes','syscomments','sysfiles1','syspermissions','sysusers','sysproperties','sysdepends','sysreferences','sysfulltextcatalogs','sysindexkeys','sysforeignkeys','sysmembers','sysprotects','sysfulltextnotify','sysfiles','sysfilegroups')
              )
         )
     and not (name = 'dtproperties')
     and not (name like 'dt%' and type = 'P')

else

  select
     name, xtype, id, crdate, crdate
  from
    [$DB_NAME$].dbo.sysobjects
  where
     xtype in ('U','S','V','P','X','RF','FN','TF','IF')
     and uid = :SCHEMA_ID
     and not (name = 'dtproperties')
     and not (name like 'dt%' and xtype = 'P')

Thu Aug 25, 2016 3:57 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
I'm guessing that :SCHEMA_ID is 1 if no schema is selected. Is that correct?
Thu Aug 25, 2016 3:59 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
Quote:
I'm guessing that :SCHEMA_ID is 1 if no schema is selected. Is that correct?


Not exactly, but you are very close. If it cannot find the object selected without schema name specified and it is instructed to qualify object name, then your default schema is used, which is 'dbo' schema in your case (could have been some other schema). That's why you see 'dbo. added to the selected name.


Conceptually, by design, the selection is started with database first, if no selected, it assumed to be current database, then schema, again if not selected, then it's assumed to be current schema, and only then the object. So if you start with the object and skip other steps, then current database and current schema are assumed.

Hope it helps.
Fri Aug 26, 2016 12:56 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
However, if a specific object is selected, shouldn't it calculate the exact database/schema rather than inferring, based on defaults?
Mon Aug 29, 2016 10:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
Code:
if a specific object is selected, shouldn't it calculate the exact database/schema

Yes it should, in case you select database then schema and then object. But if you start with the object, how would it know where it is from?
Am I getting your question correctly?
Mon Aug 29, 2016 2:59 pm View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Reply with quote
If I select from the list and an object is selected. Included in that selection, based on the query, is the object_id (which is unique per database). If a schema is not selected, could you search the default (current) database's objects to determine the correct schema? I suppose this is an enhancement request. It would be useful to allow searching for an object within a database and you aren't sure of which schema it is a member.
Mon Aug 29, 2016 5:27 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6509

Post Reply with quote
In theory yes, it could do that, but... The search is indeed is performed in the memory cache, not in the database, and it's database type agnostic, that's why if a cache is not loaded for the parent database scope, and/or not loaded for the parent schema scope, then an object cannot be matched to what's in the case. The result is what I tried describing before. if database and schema isn't specified then it's assumed that the object belongs to the current scope, whichever database and schema the current user's session points to.
Mon Aug 29, 2016 7:52 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 1401

Post Re: Cross Schema Search Reply with quote
judahr wrote:
I'd like to be able to start a Select as:

Code:
Select * from

CTRL+SPACE would show me: schemas, databases, and tables/views/etc within this database, across all schemas since a schema is not provided. I have the query working for this.

However, when I select the table, it gives it fully qualified with dbo schema, which is not the correct schema.


judahr, you get non-schema and non-database objects (tables/views/etc) across all schemas upon pressing CTRL+SPACE? How? I guess it was the extra
Code:

                     OR
           (:SCHEMA_ID IS NULL)
         or (:SCHEMA_ID =1 and user_name() <> 'dbo' and schema_id <> 4)

responsible for that but even replacing my DB Query for Objects (MSSQL) + Typed Synonyms with yours would not yield the objects from other schemas. It would be great if this could work. It could spare a few seconds/minutes of scanning for objects in the database explorer for those ones whose schema I cannot remember.
Tue Aug 30, 2016 4:35 am View user's profile Send private message
judahr



Joined: 09 Mar 2007
Posts: 314
Country: United States

Post Re: Cross Schema Search Reply with quote
gemisigo wrote:
It would be great if this could work. It could spare a few seconds/minutes of scanning for objects in the database explorer for those ones whose schema I cannot remember.


So that is the idea.

SysOp wrote:
In theory yes, it could do that, but... The search is indeed is performed in the memory cache, not in the database, and it's database type agnostic, that's why if a cache is not loaded for the parent database scope, and/or not loaded for the parent schema scope, then an object cannot be matched to what's in the case. The result is what I tried describing before. if database and schema isn't specified then it's assumed that the object belongs to the current scope, whichever database and schema the current user's session points to.


When the list of tables from the query is shown, will the memory cache ever only have tables and not schemas? Could the schemas be preloaded? It seems there should be very few schemas (at least compared to tables) within a database. I understand the inconsistent behavior that can happen if sometimes the memory cache has schemas and sometimes not.

Also it would be nice if :SCHEMA_ID could be null if the schema is not selected. Then you could do a lot more with the query such as showing the default schema or showing all objects within a schema if it is null. Then show only the objects in the selected schema if it is chosen.
Tue Aug 30, 2016 9:47 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.