Irfan's World

November 1, 2011

How to use undocumented functions of SQL Server ?

Here in this post, I am going to explain some undocumented functions of SQL Server.  These functions are very useful to get the information about a database or a table quickly as a part of maintenance and other daily tasks.  I regularly use sp_MSforeachtable to count the rows of the tables in our data warehouse and the source systems.  I also use sp_MSforeachDB  to get a count of objects in a database when a production database is refreshed on to a UAT or DEV environments.

  To get a count of objects for all the databases on a Host

sp_msforeachdb ‘select ”?” as DatabaseName, count(*) as CountObjects from ?.sys.objects’

  To get a count of rows from all the tables in a database

sp_msforeachtable ‘select ”?”, Count(*) from ?’


Note:  There are single quotes around the question mark(?) in the above syntax.

Blog at WordPress.com.