获取 多个数据库 下的所有表的 数据条数

目的：

给定一些数据库的名字，执行该SQL将得到这些数据库下面的表的记录条数

设计：

     1.首先将所有数据库下的所有的表明 存放在一个临时表中(#tmp)

     2.然后使用游标对#tmp进行遍历

具体SQL如下：

IF OBJECT_ID ('tempdb..#tmp' ) IS NOT NULL
       DROP TABLE #tmp


CREATE TABLE #tmp(
            tableName varchar(50 )
)

--catalog Cur
Declare @catalogName varchar (50)
Declare catalogCur Cursor For select t. catalogName from
(
select 'INVOICE' as catalogName union all
select 'PROP' as catalogName union all
select 'CORP' as catalogName union all
select 'GENOME' as catalogName ) as t   --注意：这里需要使用别名
 

Open catalogCur
Fetch next From catalogCur Into @catalogName
While @@fetch_status =0    
       Begin
            exec('USE ' +@catalogName+ '
            insert into #tmp
            SELECT  TABLE_CATALOG+''.''+TABLE_SCHEMA+''.''+TABLE_NAME
            FROM information_schema.tables where TABLE_TYPE <> ''VIEW''' )
			--注意：TABLE_TYPE有两种类型：BASE TABLE和VIEW，这里我们就不需要View，所以将它过滤掉

              
             Fetch Next From catalogCur Into @catalogName
       End  
Close catalogCur  
Deallocate catalogCur


-----------tableCur
declare @tablename varchar (100)
Declare @sql varchar (max) = ''
Declare tableCur Cursor for select * from #tmp
open tableCur
fetch next from tableCur into @tablename
While @@fetch_status =0
BEGIN
       set @sql = @sql + 'select COUNT(1) as recordCount,'''+@tablename+ ''' as tableName from '+@tablename +' union all'+char( 13)
       fetch next from tableCur into @tablename
END
close tableCur
deallocate tableCur
set @sql = SUBSTRING( @sql,1 ,len( @sql)-10 )
--print @sql
exec (@sql )   --注意：这里需要用括号将@sql括起来，否则会报类似下面的错误：The name '' is not a valid identifier.