Solved

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

Posted on 2001-06-12
9
228 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DIR issue 7 51
VBA error replacing data 6 37
Help me. 3 48
Excel VBA - Run Time error '1004' Application-defined or object-defined error 4 27
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

861 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

24 Experts available now in Live!

Get 1:1 Help Now