Cross Schema Search

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.

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?

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')

I'm guessing that :SCHEMA_ID is 1 if no schema is selected. Is that correct?

	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.

However, if a specific object is selected, shouldn't it calculate the exact database/schema rather than inferring, based on defaults?

	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?

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.

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.

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.

	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.