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 usesp_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
'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
'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)
IF not Exists(select s.object_id from sys.columns s where s.name=''entrydate'' and s.object_id=Object_ID(''?''))
alter table ? add entrydate datetime
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(''?''))
alter table ? drop column entrydate
execute sp_msforeachtable @command1=@SqlForAddingColumn