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

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

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.

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

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

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.

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