Using SysObjects Table

20 pts.
Tags:
SQL
Sysobjects
I have a database that I need to know what tables are in it and also what views. I also need to know what tables are used in the views. Is this possible with the Sysobjects table? Any help would be greatly appreciated. TIA SQL Newby
ASKED: March 3, 2008  3:35 PM
UPDATED: March 4, 2008  7:12 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes you can use the sysobjects table for this.

To find the tables you need to look for objects with the xtype = ‘U’, views have an xtype = ‘V’ and procedures have an xtype = ‘P’

<pre>SELECT name
FROM sysobjects
WHERE name = ‘U’</pre>

If you have SQL Server 2005 and up the sysobjects table has been replaced by various system management views.

<pre>SELECT name
FROM sys.tables</pre>

<pre>SELECT name
FROM sys.views</pre>

You’ll want to look at the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and INFORMATION_SCHEMA.VIEW_TABLE_USAGE system views. The INFORMATION_SCHEMA.VIEW_TABLE_USAGE view shows which tables make up which views. The INFORMATION_SCHEMA.VIEW_COLUMN_USAGE view shows which columns make up the views (also handy information to have some times).

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    65,450 pointsBadges:
    report
  • Gdckboyer
    MrDenny, Thanks for the response. I did find it useful, but the other part of my question remains. Is it possible to find what tables are used to make up a view using sysobjects?
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following