How to enumerate the databases on the server

Given that you have an interface with a connection to SQL Server Express, is there some T-SQL script or statement that can enumerate the available database names on the server ?

For example:

CString clQuery;

clQuery.Format("SELECT SERVERPROPERTY('Databases')");

m_db.Execute(clQuery);

or something of the sort ?

This will work in both SQL 2000 and SQL 2005:

Code Snippet

SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA

|||

Thanks, Arnie.

For some reason, that statement only yields about 16 'Master' records in the CATALOG_NAME column. I really need a way to enumerate all the databases on the server.

Any other ideas ?

Mike

|||

Sorry Mike, my mistake. That Information VIEW 'changed' between SQL 2000 and SQL 2005.

In SQL 2005, use:

SELECT name

FROM sys.databases

|||

Thanks, Arnie. That works perfectly.

BTW, I have had trouble responding as your server appears to be down quite a bit of the time.

Regards,

Mike

|||There seems to have been some 'replication' issues this week. We all appreciate everyone's patience/

How to enable full text search for symbols?

scenario:
I have a user interface done in Coldfusion that access SQL server 2K store
procedure on server 2003 that perform full text search on a field of a table.
I have remove all content of noise.enu as user can use anything as a search
index.
Why is it that SQL server or SQL Analyser still return err/msg when I use
symbol as serach index (e.g. {/}/?/>/</./,/@./!/$):
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words." - error msg from SQL Analyser
At minimun SQL server should return no recors. Instead it return err/msg,
that hangs my SQL service when run from Coldfusion and i had to manual
restart my service. Any ideas ?
These are invalid characters which cannot be searched upon. This should not
be hanging your SQL Server however. You should be trapping for such
characters on the client and removing them if the user is trying to search
on them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:95366FB3-4621-421E-9F5B-DD3118798A5F@.microsoft.com...
> scenario:
> I have a user interface done in Coldfusion that access SQL server 2K
> store
> procedure on server 2003 that perform full text search on a field of a
> table.
> I have remove all content of noise.enu as user can use anything as a
> search
> index.
> Why is it that SQL server or SQL Analyser still return err/msg when I use
> symbol as serach index (e.g. {/}/?/>/</./,/@./!/$):
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words." - error msg from SQL Analyser
> At minimun SQL server should return no recors. Instead it return err/msg,
> that hangs my SQL service when run from Coldfusion and i had to manual
> restart my service. Any ideas ?
|||well, i can code a filter at the coldfusion side. But i have to test all
possible invalid chars for SQL server 2K Full text search.
Is there any easy way out whereby I can configure SQL server 2K to return at
least no records or trap the err/msg return in SQL server as a result of
invalid chars in full text search.
xxx
"Hilary Cotter" wrote:

> These are invalid characters which cannot be searched upon. This should not
> be hanging your SQL Server however. You should be trapping for such
> characters on the client and removing them if the user is trying to search
> on them.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "eslim" <eslim@.discussions.microsoft.com> wrote in message
> news:95366FB3-4621-421E-9F5B-DD3118798A5F@.microsoft.com...
>
>