?
Solved

Using ADODB to query the owner of a particular table in a database

Posted on 2001-06-12
9
Medium Priority
?
241 Views
Last Modified: 2010-05-02
Is there any way of getting the owner of a table from a database (SQL Server 7), using ADO via a dsn connection.

Something like:

Dim tableOwner

tableOwner = con.getTableOwner("the table name")

any help would be greatly appreciated,

I'll up the points if code is provided instead of information or url's to visit
0
Comment
Question by:MattC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Author Comment

by:MattC
ID: 6180781
Or a stored proceudre I could use to return the info I need from the database
0
 
LVL 1

Expert Comment

by:morgan_peat
ID: 6180818
system stored procedure sp_tables should return what you want:

EXEC sp_tables
Possible parameters include:
@table_name = '<target table name>'
@table_owner = '<owner>'

So (to be very obvious about it) in VB:
Set oRSData = oCon.Execute("exec sp_tables")

Here's a URL with more info about the stored proc (just because you said you didn't want any URL's :-) )
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ts_sp_ta-tz_6ucz.htm
0
 
LVL 1

Expert Comment

by:morgan_peat
ID: 6180821
Sorry - forgot to put down what it returns.
Fields returned from this proc are:
DB Name
Table Name
Table Owner
Table Type (system table, table, view)
Remarks (always seems to be null)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6180873
select user_name(uid), name from sysobjects where id = object_id('YourTableName')

Cheers
0
 

Expert Comment

by:rsuzi
ID: 6180906
If you can live with querying system tables (they have no PK) you can obtain the name of the owner of a user table with the following query:
select name from sysusers where uid in (select uid from sysobjects  where xtype='u' and name='tableName')
0
 
LVL 1

Author Comment

by:MattC
ID: 6181356
getting closer to what I want with EXEC sp_tables
0
 
LVL 1

Author Comment

by:MattC
ID: 6181399
Morgan_peat,
I have this now


sqlStat = "Exec sp_tables
.
.
.
.
cmd.CommandText = sqlStat
Set rst = cmd.Execute
.
.
.


How do I query the recordset to pick out just the table owner
0
 
LVL 1

Accepted Solution

by:
morgan_peat earned 400 total points
ID: 6181478
To get owner for a table do:

    Set oRSData = oCon.Execute("exec sp_tables @table_name='<table name>'")
    sOwner = oRSData("table_owner")
0
 
LVL 1

Author Comment

by:MattC
ID: 6181669
That's the one!

This was how I did it

sqlStat = "EXEC sp_tables '" & tablename & "'"
   
command.CommandText = sqlStat
Set record = command.Execute

Cheers
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question