Create Procedure Check_Table_Count
as 
declare @dbname varchar(50) 
       ,@dbid int 
       ,@table_catalog varchar(50) 
       ,@table_schema varchar(50) 
       ,@table_name varchar(50) 
       ,@table_type varchar(10)   
       ,@message nchar(100) 
       ,@string1 varchar(255) 
       ,@string2 varchar(255)
       ,@string3 varchar(255) 
       ,@string4 varchar(255) 
       ,@string5 varchar(125)
       ,@execstring varchar(255)  
       ,@switchdb  varchar(75)      
    
select 'Tables in Databases on Server  ' +
       @@servername 

DECLARE Retrieve_Databases CURSOR FOR 
SELECT name,dbid
FROM master..sysdatabases

 OPEN Retrieve_Databases

  FETCH NEXT FROM Retrieve_Databases
  INTO @dbname, @dbid
  select @dbname as 'db name'

		WHILE @@FETCH_STATUS = 0
		  BEGIN

			set @string1 = 'DECLARE RETRIEVE_TABLES CURSOR FOR ' 
			set @string2 = 'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM '
			set @string3 = rtrim(@dbname) + '.INFORMATION_SCHEMA.TABLES '
			select @string4 = 'WHERE TABLE_SCHEMA <> ''INFORMATION_SCHEMA'' and TABLE_CATALOG = ''' + rtrim(@dbname) + ''' '


			select @string5 =  'and TABLE_NAME NOT IN (''' + 'dtproperties' + '''' + ',' + '''' + 'sysalternates'
				+ ''''+ ',' + '''' + 'sysconstraints'',' + '''' + +  'syssegments''' + ')'

			exec (@string1 + @string2 + @string3 + @string4 + @string5) 
                         
			
                        if cursor_status('global','retrieve_tables') =  -1
	                      OPEN RETRIEVE_TABLES

 			 FETCH NEXT FROM RETRIEVE_TABLES INTO 
  				 @table_catalog, @table_schema, @table_name , @table_type
                      
				
	   				WHILE @@FETCH_STATUS = 0
   					 BEGIN
      
      	               	 			select @message = rtrim(@table_catalog) + '.' + rtrim(@table_schema) + 
              		         		'.' + rtrim(@table_name) 
     					
                                                if @table_type = 'base table' and @table_name not in('tempdb','master','msdb','model','pubs','northwind')
                                                     begin 

								
								select @string1 = 'use ' +  rtrim(@table_catalog) + ' '


								select @string2 = 'insert dba_fieldglass..servertablehistory 
										   (name 
										   ,numberofrows 
										   ,reserved
										   ,data
										   ,index_size
										   ,unused
										    )' 

  
			
								select @string3 =  'exec sp_spaceused @objname= ''' +   @message + '''' 
 
								select @string1 + @string2 + @string3  as 'Execute String'
								exec( @string1 + @string2 + @string3 )
			                               
				                     end
   			                       FETCH NEXT FROM RETRIEVE_TABLES INTO 
     				                 @table_catalog, @table_schema, @table_name, @table_type 
                                          END

			            CLOSE RETRIEVE_TABLES
   			            DEALLOCATE RETRIEVE_TABLES
 			
	
     			FETCH NEXT FROM Retrieve_Databases
     			INTO @dbname, @dbid
                        select @dbname as 'database name'
 		  END 
 CLOSE Retrieve_Databases
 DEALLOCATE Retrieve_Databases

return

quote:

---

If you dig around in sp_spaceused (see what it is doing and such), you can probably find a pretty easy way to do this every table in each database pretty easily without the table cursor.

---

quote:

---

Also, the system procedures Todd mentioned are sp_msforeachtable and sp_msforeachdb.

---

I was pretty close.