To get the no of tables from db

create proc get_table_count as 

begin

create table #temp(database_id int,name varchar(30))

declare @res_table table(database_id int,name varchar(30),tables_count int)

insert into #temp

select database_id,name from sys.databases

declare @cnt1 int,@str nvarchar(1000),@i int,@db_name varchar(100),@db_id int

select @cnt1=count(1) from #temp

select @i=1

while(@i<=@cnt1)

begin

select @db_name=name,@db_id=database_id from #temp where database_id=@i

select @str= 'select '+cast(@db_id as varchar(10)) +','+char(39)+@db_name+char(39)+', count(1) from '+@db_name+'.sys.tables'

insert into @res_table

exec sp_executesql @str

select @i=@i+1

print @str

end

select * from @res_table

end