sp_msforeachtable Usage

            sp_msforeachtable 

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

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