Monday, April 22, 2013

Sql Server : How to get number of records in each table of a database?

Question: How to get number of records in each table of a database?

Solution:  one way is to get count from each table by specifying table name, and another way is to use sp_msforeachtable stored procedure.
sp_msforeachtable 'select ''?'' ,count(*) from ?''

This stored procedure can also be used for various other operations also, these are as below:

How to get number of rows in some of the tables of a database

sp_msforeachtable
'if ''?'' in (''[dbo].[ABC]'')
select ''?'' as table_name , count(*) as number_of_rows from ? '

---how to get tables which are having 0 rows, or the emplty table

sp_msforeachtable
'select ''?'' as table_name , count(*) as number_of_rows from ? having count(*)=0'

--Alter all table and enable trigger on all of the table
sp_msforeachtable 'alter table ? enable trigger all'
Using all other parameters of sp_msforeachtable , we can use below parameter as per requirment
@command1 – for giving first command that will execute for all tables
@command2 –this command will execute next to command1
@command3- this command will execute next to command2

@whereand – it has another parameter called @whereand, which is appended to the WHERE clause of the internal query that is being used to find the tables (and should start with an AND).
One can also use aliases like o against sysobjects, and a second alias syso against sys.all_objects.

@precommand- is the command which will be executed before all othercommands defined in the parameters of sp_msforeachtable
@postcommand - is the command which will be executed after all othercommands defined in the parameters of sp_msforeachtable


For displaying the tables which names are in the whereand condition, along with tables, it will also display current database name, before the result of @command1 and after the result of @command1

 



If we want to add some column in all the tables, like , if we want to add entrydate column to record the time of entry , we can do it by using sp_msforeachtable as shown below:

declare @SqlForAddingColumn varchar(2000)
set @SqlForAddingColumn='
IF not Exists(select s.object_id from sys.columns s where s.name=''entrydate'' and s.object_id=Object_ID(''?''))
begin
alter table ? add entrydate datetime
end
'
declare @SqlForRemovingColumn varchar(2000)
set @SqlForRemovingColumn='IF Exists(select s.object_id from sys.columns s where s.name=''entrydate'' and s.object_id=Object_ID(''?''))
begin
alter table ? drop  column entrydate
end'


execute sp_msforeachtable @command1=@SqlForAddingColumn
,@command2=@SqlForRemovingColumn

Please provide your feedback for the post, if you find this post useful. Also Post your query or scenario, i will be happy to help.

No comments:

Post a Comment

Post a Comment