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