SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Querying System Tables


Querying System Tables

Author
Message
Vasant Raj
Vasant Raj
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 137
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp
Phil.Nicholas
Phil.Nicholas
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 10
A great way of finding out more about system tables is to look at the system stored procedures in the master database, whenever I need to find out something about the db metadata that isnt commonly known this is the place to start. sp_help for example is a great place to look for the the things mentioned in the article.

btw the I think the identity column is identified by (status & 128) = 128 as its a bitwise column.

If you need to look at undocumented functionality trace while doing what you want to do in enterprise manager then take a closer look at the releavant sps, obviously be cautious about using unsupported system sps, columns etc!


Phil Nicholas
Manu Dutt
Manu Dutt
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 1

A good summary to all the system tables ... I feel if some one master these tables he/she will get more out of SQL Server ...

Regards
MD




Hi! Its - Manu Dutt
Visit me at - manudutt.co.nr
Jose Torres
Jose Torres
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 1

just a comment, in the syscolumns table there's another way to get the names and easier for me to remember

instead of querying the table as you do, you could also use the OBJECT_ID function wich does the same as the subquery you use there

SELECT name FROM syscolumns WHERE ID = OBJECT_ID('XYZ')




Kindest Regards from Mexico,

Jose Torres
Carlos Urbina
Carlos Urbina
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 1

Great article! I will keep it as a reference.

In a future article, you could say something about the views in the master database, such as tables, columns, etc.


Mark Yelton
Mark Yelton
Mr or Mrs. 500
Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)Mr or Mrs. 500 (500 reputation)

Group: General Forum Members
Points: 500 Visits: 433

I appreciate articles like this; even though I've been a DBA for a while I almost always see something in this type of article that I didn't know. I think it's always good to go back and revisit things we learned when we first started as a DBA. Also, this type of article is a great tool to give to junior DBAs trying to find introductory SQL Server information. I'm currently bringing on a junior DBA and I gave him the link to this article; he WILL read it. LOL

Mark


R Michael
R Michael
SSC Eights!
SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)SSC Eights! (987 reputation)

Group: General Forum Members
Points: 987 Visits: 275
Very good article!

I thought I would share another good resource on this topic. Microsoft has a system table map available on their website.

http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx

-Robert

SQL guy and Houston Magician
John Robinson-146225
John Robinson-146225
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 5

Good article.

I find using the system table invaluable. I thought I would share a simple query of the system tables that I use to find tables and columns where I supply a portion of what I might think a column name would contain. I find this quite useful when you have a large, complex database and you don't know exactly which tables you may want to look in...much easier than using Enterprise Manager.

DECLARE @Column sysname
SELECT @Column = UPPER ('%STRING%')
-- Replace 'STRING' with the portion of the column name you wish to search for
--
SELECT sysobjects.name AS Table_name, sysobjects.xtype, syscolumns.name AS Column_name, systypes.name AS Column_Type, syscolumns.length AS Column_length, systypes.allownulls AS Column_nullable
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype = systypes.xusertype
WHERE UPPER (syscolumns.name) LIKE @Column
-- choose to limit to just tables, just views, or both by commenting out the ones you don't want
AND sysobjects.xtype = 'U' -- tables only
-- AND sysobjects.xtype = 'V' -- views
-- AND sysobjects.xtype IN ('U', 'V') -- tables and views
ORDER BY sysobjects.type, sysobjects.name


michanne
michanne
Mr or Mrs. 500
Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)Mr or Mrs. 500 (595 reputation)

Group: General Forum Members
Points: 595 Visits: 182

While the information you are getting is invaluable --why make systables your first stop? Information_schema views are much more user-friendly. (Table_name vs object_name(id)). And they upgrade better.

Only bugger with Information_Schema views -- Indexes are unavailable and you must resort to a function or sysindexes.





Steve Rosenbach
Steve Rosenbach
SSC-Addicted
SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)SSC-Addicted (467 reputation)

Group: General Forum Members
Points: 467 Visits: 206

Very good post Vasant, and I also appreciate John Robinson's script for finding tables or columns from a portion of the name.

After several years of using SQL 2000, I stumbled across the fact that the functionality of John's script, searching for an object by a partial name, is built into Query Analyzer!

Just click on the menu: Tools|Object Search|New - this brings up a dialog with lots of options - you can search for various types of objects (tables, columns, views, etc) in your choice of database or across all the databases on the server.

As my contribution to this thread, here is a quick-and-dirty way of getting table row counts for a database:

SELECT
so.[name] tableName
, si.rowcnt [RowCount]
FROM sysindexes si
JOIN sysobjects so ON si.id = so.id
WHERE
si.indid IN(1,0)
AND so.xtype = 'U'

Of course, you can ORDER BY so.[name] to alphabetize the result or by si.rowcnt to order by number of rows, and so on.

Just be aware that if the Statistics for the indexes are not up-to-date, the rowcounts won't be correct - in fact, they could be way off. But for a lot of situations, this little script it good enough, and it's MUCH faster than looping through the tables and doing a SELECT count(*) FROM TableName for each.

Best regards,
SteveR





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search