Solved

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

Posted on 2001-06-12
9
237 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 100 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

734 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