Friday, May 24, 2013

sp_MSforeachtable

Today, Grant Fritchey tweeted about sp_MSforeachtable . What will we see if we run the following statement on Azure database?

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

It retruns
Could not find stored procedure sp_msforeachtable.

It is deprecated on Azure. Then, I tried to run on local development database. It returns total record count in each table in database. It will be useful for a DBA in future. Next step, I looked up sp_Msforeachtable in Google. I found two more useful scripts.

Checking integrity of tables in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'DBCC CHECKTABLE ([?])';

Checking space used of each table in database

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];';


Checking space used of each database

declare @cmd varchar(500)
set @cmd='use [?];exec sp_spaceused '

exec sp_MSforeachdb @cmd

No comments:

Post a Comment

How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...