February 28, 2007 at 9:39 am
Hello Everyone,
I need to list the number of tables in each database on our production server. Can someone tell me how to do this?
Thank you so much!
Juanita
February 28, 2007 at 9:58 am
sp_msforeachdb 'select ''?'' as db_name, count(*) as table_count from ?.dbo.sysobjects where xtype = ''u'''
That should do it.
February 28, 2007 at 10:02 am
This query gives the count of user tables and the system tables. Run this query in the database you want to
select count(*) from sysobjects
where xtype in ('U','S')
'U' = user table
'S' = system table
if you want just the user tables query with xtype = 'U'.
Hope thsi helps
February 28, 2007 at 11:25 am
Thank you for the suggestions! They do the job!
Juanita
March 30, 2007 at 11:54 am
Hi,
Is there any DMV in SQL 2005 to get this information directly?
TIA
Sandesh
March 30, 2007 at 1:13 pm
If you only want user tables, and if you don't mind information schema view (they're system table independent), then the following will work:
SELECT
Count(TABLE_NAME)
FROM
INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
Note that you have to manually exclude "dtproperties", which is true of the other scripts in this thread as well. For some reason, that is considered a user table even though it's for storing database diagrams. Remove the "Count()" if you want the names themselves. If you want to count system tables as well, then don't use the information schema view method, as those aren't included.
March 30, 2007 at 2:03 pm
Thanks David,
I will have to run this from databases. If i would like to collect list of all table in all databases present on server, is there any system DMV? I would like to avoid cursor/Temp table/Table variable to write query.
TIA
Sandesh
March 30, 2007 at 3:58 pm
I'm not aware of any DMVs that would return everything you need, but only a few of our servers are 2005 at this time, so I haven't had a lot of time to play around with them yet. If you want to run against all databases without writing a cursor, you can use the undocumented "sp_msforeachdb" stored proc that Aaron mentions above (well, it's technically a cursor, but at least you don't have to write it). That stored proc can work with any of the scripts we've provided, so mix and match depending on your needs. If that stored proc is new to you, you'll be happy to know that it has a partner stored proc, named "sp_msforeachtable", which is the same concept, but for all tables within a database.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply