Enumerating SQL Server database tables rows and sprocs using MFC

Hi,
I've been looking around for a while now after an efficient way to enumerate
tables, rows and sprocs of a MsSQL database. The best I could come up with
was SQL DMO, but one cannot have it installed without installing SQL Server
itself or MSDE, so I'm looking for alternative.
After a table name was fetched, I will need to know the rows it contains,
what type they are, and what are the indexes and identity defined for this
table. Effectively, if I could somehow get the CREATE SQL statement from the
DB itself that would be ideal. Also, I will need a way to get the sprocs
code from the DB, and all that without having any prequisites on either
sides - client and server.
I'm using VC++ with MFC. No managed C++ or .NET, unless I can use the
algorythm in an unmanaged enviroment.
Any links, articles, replies and sample code would be highly appreciated.
Thanks in advance,
Stilgar.Stilgar wrote:
> Hi,
> I've been looking around for a while now after an efficient way to enumera
te
> tables, rows and sprocs of a MsSQL database. The best I could come up with
> was SQL DMO, but one cannot have it installed without installing SQL Serve
r
> itself or MSDE, so I'm looking for alternative.
> After a table name was fetched, I will need to know the rows it contains,
> what type they are, and what are the indexes and identity defined for this
> table. Effectively, if I could somehow get the CREATE SQL statement from t
he
> DB itself that would be ideal. Also, I will need a way to get the sprocs
> code from the DB, and all that without having any prequisites on either
> sides - client and server.
> I'm using VC++ with MFC. No managed C++ or .NET, unless I can use the
> algorythm in an unmanaged enviroment.
> Any links, articles, replies and sample code would be highly appreciated.
> Thanks in advance,
> Stilgar.
To connect to the database you'll normally need an ODBC or OLEDB driver
and the usual way to get that is to install the client connectivity
from the SQL Server disc or install MDAC from microsoft.com (unless you
plan to write your own ODBC/OLEDB client).
The metadata is available in the ADO object model or through TSQL
commands that return a recordset. SQL Server provides the Information
Schema views for that purpose and you can read about them in Books
Online.
David Portas
SQL Server MVP
--|||Will DESCRIBE <tablename> work on MsSql?
For each row simply do SELECT * FROM <table>
- MR
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:%23RANlXE7FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've been looking around for a while now after an efficient way to
> enumerate tables, rows and sprocs of a MsSQL database.|||DESCRIBE <tablename> doesn't seem to work with MsSQL. I've executed it and
it errored.
How can I get the field types using SELECT *? Not to mention indexes and
determine what is the identity field?
Stilgar.
"Mark Randall" <markyr@.gmail.com> wrote in message
news:%23oLYohE7FHA.3588@.TK2MSFTNGP15.phx.gbl...
> Will DESCRIBE <tablename> work on MsSql?
> For each row simply do SELECT * FROM <table>
> - MR
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:%23RANlXE7FHA.3876@.TK2MSFTNGP09.phx.gbl...
>|||I'm using ODBC. Can you shortlist the main TSQL commands? I couldn't find
them myself, as I don't know what to look for exactly here.
How can MDAC help here?
Stilgar.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132322428.645339.183190@.g44g2000cwa.googlegroups.com...
> Stilgar wrote:
> To connect to the database you'll normally need an ODBC or OLEDB driver
> and the usual way to get that is to install the client connectivity
> from the SQL Server disc or install MDAC from microsoft.com (unless you
> plan to write your own ODBC/OLEDB client).
> The metadata is available in the ADO object model or through TSQL
> commands that return a recordset. SQL Server provides the Information
> Schema views for that purpose and you can read about them in Books
> Online.
> --
> David Portas
> SQL Server MVP
> --
>|||http://msdn.microsoft.com/library/d...r />
_9sfo.asp
Click 'Up One Level' and there you go ... I tried to give you that direct
link but ...
-Mark
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:%231s$CpE7FHA.476@.TK2MSFTNGP15.phx.gbl...
> I'm using ODBC. Can you shortlist the main TSQL commands? I couldn't find
> them myself, as I don't know what to look for exactly here.
> How can MDAC help here?
> Stilgar.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1132322428.645339.183190@.g44g2000cwa.googlegroups.com...
>|||Hello Stilgar,
Take a look at the GetSchema() method using the most recent SqlClient bit
drops. BobB has good article about them at [0], but that's framed in ADO.NET
terms. I believe you can use the same patterns in C++/MFC, however. Just
harder :)
[0] http://msdn.microsoft.com/library/d...net2schemas.asp
Long live the fighters!
Kent Tegels
DevelopMentor|||I got an even better one - EXEC sp_tables, sp_columns <table>,
sp_stored_procedures.
Thanks for your help.
Stilgar.
"Mark Nijhof" <Mark@.Nijhof.com> wrote in message
news:urPdcHF7FHA.1184@.TK2MSFTNGP12.phx.gbl...
> http://msdn.microsoft.com/library/d.../>
es_9sfo.asp
> Click 'Up One Level' and there you go ... I tried to give you that direct
> link but ...
> -Mark
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:%231s$CpE7FHA.476@.TK2MSFTNGP15.phx.gbl...
>|||...And I have an even better Idea;
go to
http://dev.mysql.com/downloads/
and download the MySQL++, this makes connecting from VC++ to a MySQL
database really easy, no ODBC, or any other connectors, straight to the
MySQL database.
John
The_Code_Master@.hotmail.com
"Stilgar" <stilgar@.divrei-tora.com> wrote in message
news:eg$xYBU7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> I got an even better one - EXEC sp_tables, sp_columns <table>,
> sp_stored_procedures.
> Thanks for your help.
> Stilgar.
> "Mark Nijhof" <Mark@.Nijhof.com> wrote in message
> news:urPdcHF7FHA.1184@.TK2MSFTNGP12.phx.gbl...
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sa-ses_9sfo.asp
direct
find
message
either
driver
you
>|||Sure, only problem is I asked about MS SQL Server, not MySQL. Thanks, but
I'm already familiar with it.
Stilgar.
"The Code Master" <The_Code_Master@.hotmail.com> wrote in message
news:BPOff.21604$tV6.12458@.newssvr27.news.prodigy.net...
> ...And I have an even better Idea;
> go to
> http://dev.mysql.com/downloads/
> and download the MySQL++, this makes connecting from VC++ to a MySQL
> database really easy, no ODBC, or any other connectors, straight to the
> MySQL database.
> John
> The_Code_Master@.hotmail.com
>
> "Stilgar" <stilgar@.divrei-tora.com> wrote in message
> news:eg$xYBU7FHA.2036@.TK2MSFTNGP14.phx.gbl...
> [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_[/ur
l]
> sa-ses_9sfo.asp
> direct
> find
> message
> either
> driver
> you
>