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.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: