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

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
--Kamel Gazzah
--This procedure stores in a table called "Utab_counts" for each table
-- in the current data base the number of records
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
print @script
fetch c into @script
close c
deallocate c

select * from Utab_counts order by N desc

 Subscribe to Articles


Further Readings:

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...


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