asked on

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

MattC

ASKER

Or a stored proceudre I could use to return the info I need from the database

morgan_peat

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

morgan_peat

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)

Guy Hengel [angelIII / a3]

select user_name(uid), name from sysobjects where id = object_id('YourTableName')

Cheers

rsuzi

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')

MattC

ASKER

getting closer to what I want with EXEC sp_tables

MattC

ASKER

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

ASKER CERTIFIED SOLUTION

morgan_peat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MattC

ASKER

That's the one!

This was how I did it

sqlStat = "EXEC sp_tables '" & tablename & "'"

command.CommandText = sqlStat
Set record = command.Execute

Cheers