How to read DATABASES names from SQL server

[avista]

Hi all
I have INFORMIX server with few databases.
Please some help:

1. How to read a list of DATABASES which exist on server (get list of databases)
Something like: oOdbc:GetTables() for getting tables from database.

2. How get a list of ROUTINES (procedures) for each of databases

3. How to read content (text) of the some routine (procedure)

Best regards,

[avista]

NOONE ?

[carlos vargas]

sorry, i dont use mssql, but in ads i use the sql instruccion

SELECT * FROM system.TABLES

this return me a cursor with a tables in the database, can you search a similar intrucction in mssql.


salu2

[anserkk]

Hi all
I have INFORMIX server with few databases.
Please some help:

1. How to read a list of DATABASES which exist on server (get list of databases)
Something like: oOdbc:GetTables() for getting tables from database.

2. How get a list of ROUTINES (procedures) for each of databases

3. How to read content (text) of the some routine (procedure)

Best regards,

Code: Select all  Expand view

//  List of databases
cSql:="SELECT name FROM master.dbo.sysdatabases ORDER BY name"

// List of tables from a database
cSql:="SELECT * FROM YourDatabaseName.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'"  

// List of user created stored procedures from the connected database, works from SQL 2005 onwards
cSql:="SELECT * FROM sysobjects WHERE type = 'P' AND category = 0 ORDER BY name"  

// Contents of stored procdure
cSql:="SELECT text FROM syscomments WHERE id = (SELECT id FROM sysobjects WHERE type = 'P' AND category = 0) ORDER BY colid "


CursorWait()
oRecSet:= CreateObject("ADODB.RecordSet")
oRecSet:CursorLocation := adUseClient
oRecSet:LockType := adLockOptimistic
oRecSet:CursorType := adOpenDynamic
oRecSet:ActiveConnection(oApp:oConnection)
oRecSet:Source :=cSql
TRY
   oRecSet:Open()
CATCH oError
   CursorArrow()
   MsgInfo("Failed to Retrieve Recordset data from the Remote Server")
   oRecSet:=NIL
   Return .F.
END
CursorArrow()
xbrowser oRecSet

// Show stored procedure content
MsgInfo(oRecSet:Fields("text"):Value)

Regards
Anser