master.INFORMATION_SCHEMA.SCHEMATA

Is is just me or does this query produce the wrong results?
SELECT CATALOG_NAME FROM master.INFORMATION_SCHEMA.SCHEMATA
I have a MSDE sp3a installation, and this query does not include my user
database in the resut set.
hi,
Wozza wrote:
> Is is just me or does this query produce the wrong results?
> SELECT CATALOG_NAME FROM master.INFORMATION_SCHEMA.SCHEMATA
> I have a MSDE sp3a installation, and this query does not include my
> user database in the resut set.
yes... it seems to work only when running in the user's database context,
and only for the specifi user's database..
CREATE DATABASE a
CREATE DATABASE b
if run on "b" database context it returns
USE b
SELECT CATALOG_NAME FROM master.INFORMATION_SCHEMA.SCHEMATA
CATALOG_NAME
master
tempdb
model
msdb
b
(5 row(s) affected)
seems to be a problem in the
WHERE
a_cha.type = 1001 -- type is charset
AND a_cha.id = convert(tinyint, DatabasePropertyEx(db.name, 'sqlcharset'))
filter of the view,
select
db.name as CATALOG_NAME
,USER_NAME() as SCHEMA_NAME
,USER_NAME() as SCHEMA_OWNER
,convert(sysname, NULL) as DEFAULT_CHARACTER_SET_CATALOG
,convert(sysname, NULL) as DEFAULT_CHARACTER_SET_SCHEMA
,a_cha.name as DEFAULT_CHARACTER_SET_NAME
FROM
master.dbo.sysdatabases db,
master.dbo.syscharsets a_cha --charset/1001, not sortorder.
WHERE
a_cha.type = 1001 -- type is charset
AND a_cha.id = convert(tinyint, DatabasePropertyEx(db.name, 'sqlcharset'))
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||ok... SQL Server MVP Jasper Smith expanded this reporting it a a "known
issue" of that ANSI view, depending on the autoclose property (default on
MSDE) of the database(s), that causes them to not appear in the
INFORMATION_SCHEMA.SCHEMATA view
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply