Thursday, June 9, 2016

Sql server count rows in all tables

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

sp_msforeachtable 'select ''?'' ,count(*) from ?'



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

sp_msforeachtable
'if ''?'' in (''[dbo].[Account]'')
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 requirement

@command1 – 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 where and condition, along with tables, it will also display current database name, before the result of @command1 and after the result of @command1