Solved

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

Posted on 2001-06-12
9
226 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
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
 
LVL 142

Expert Comment

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

Cheers
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now