Richard Ohrvall wrote (excerpted):
I am trying to retrieve data from a SQL-database. I have no problem
loading tables using odbc list, odbc query and odbc load. However,
when I use odbc query I only see the tables in the database and not
the stored views there. I can retrieve the views by using: odbc load,
exec("select * from viewname") user(user) password(password), but it
would be helpful if I could see all the views available without having
to use another program than Stata. Does anyone know if this is
possible and if so how it is done?
--------------------------------------------------------------------------------
If you're talking about a Microsoft SQL Server database, then you can get
that information (actually, all of the database's objects) from within Stata
by executing the built-in stored procedure "sp_help". Try the sequence of
commands below, mutatis mutandis.
odbc exec("USE master;"), dsn("LocalServer")
odbc exec("EXECUTE sp_help;"), dsn("LocalServer")
You can also -odbc load- this information for inspection and use:
odbc load , exec("EXECUTE sp_help;") dsn("LocalServer")
tabulate Name if Object_type == "view"
levelsof Name if Object_type == "view", local(ListofMyDatabaseViews)
There is also a more specifie stored procedure, "sp_tables", but specifying
views-only requires surrounding the parameter with double quotes in the SQL
EXECUTE statement.
Joseph Coveney
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
