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


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

How to store and query the number of records of the tables of a sql server data base

Posted By:Kemal AL GAZZAH       Posted Date: June 29, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

This article shows how to store in a table the number of records of each table
 

In some circumstances we have to install an empty data base in client server
This data base should contain NO DATA for most of the tables but should contain
some Data for some parametring tables

I have created the following Stored procedure which stores for each table in the
sql server data base, the Number of records,  these data are stored in one table
which I called Utab_counts which has as columns the table name and the number of records

After executing the stored procedure, you can make queries onto this table.

In this procedure I used the sysobjects system table which contains all objects in data base (xtype='u' if for tables) and I used a cursor which execute the sql script
inserting the data in "Utab_counts"

 

create proc usp_tab_counts
as
--Kamel Gazzah
kamelgazzah@gmail.com
--29/06/2011
--This procedure stores in a table called "Utab_counts" for each table
-- in the current data base the number of records
---------------------------------------------------------------------------------------------------
begin
if  exists(select 1 from sysobjects where name='Utab_counts') drop table Utab_counts

if not exists(select 1 from sysobjects where name='Utab_counts') create table Utab_counts(tab nvarchar(100),N int)


declare c cursor for 
select 'insert into Utab_counts select '''+name + ''',count(1)  from '+name from sysobjects where xtype='u'
declare @script as nvarchar(1000)
open c
fetch c into @script
while @@fetch_status=0
begin
print @script
exec(@script)
fetch c into @script
end
close c
deallocate c

select * from Utab_counts order by N desc
end
Go

 Subscribe to Articles

     

Further Readings:

Responses
Author: Amit Mehra         Company URL: http://www.dotnetspark.com
Posted Date: July 04, 2011

Very nice Kamel...Nice explanation.....Thanks for sharing...Hope to see many more articles from you...

Regards,
Amit

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

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