.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!



Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

List of all tables in a database with number of records

Posted By:Kemal AL GAZZAH       Posted Date: June 03, 2016    Points: 25    Category: SQL Query    URL: http://www.dotnetspark.com  

Here a simple sql script which lists, for a given database, all the tables with the number of records for each tables
 

-- kamel Gazzah
-- sql script which lists for each table, in a given database
-- The number of records for each tables


create table #mytable (tab varchar(200),n int)
set nocount on
declare @tab as varchar(200)
declare @script as varchar(max)
declare c cursor for select name from sysobjects where xtype='u'
declare @n as int
open c
fetch c into @tab
while @@fetch_status=0
begin
set @script='insert into #mytable(tab,n) select '''+@tab+''',COUNT(1) from '+@tab;
exec(@script)
fetch c into @tab
end
close c
deallocate c
set nocount off
select * from #mytable order by N desc;
drop table #mytable



     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend