Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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

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
MattC
Asked:
MattC
1 Solution
 
MattCAuthor Commented:
Or a stored proceudre I could use to return the info I need from the database
0
 
morgan_peatCommented:
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
 
morgan_peatCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

Cheers
0
 
rsuziCommented:
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
 
MattCAuthor Commented:
getting closer to what I want with EXEC sp_tables
0
 
MattCAuthor Commented:
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
 
morgan_peatCommented:
To get owner for a table do:

    Set oRSData = oCon.Execute("exec sp_tables @table_name='<table name>'")
    sOwner = oRSData("table_owner")
0
 
MattCAuthor Commented:
That's the one!

This was how I did it

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

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now