Skip to main content

Loop through all the tables within a database to perform specific task using sp_MSforeachtable in SQL Server 2012 /2008

Beginning
There are some situation when we need to perform a specific task allmost every table within a database so we need to loop through
all tables in a database.Let we have to truncate those tables having name with a perticular prefix or we want to count rows of each table.
this article will give a tips to do this task easily using 'sp_MSforeachtable' Stored Procedure.

Details
'sp_MSforeachtable' Stored Procedure is an undocumented procedure takes place in 'master' database
Syntax of using 'sp_MSforeachtable'
use [database_name]
exec sp_MSforeachtable @command
@command is the command that has to be applied in each table

Example
To create a list of all tables with their row count you may create a user defined stored procedure as follows

CREATE PROCEDURE [dbo].[usp_RowCountForAllTables]

AS
BEGIN
   
    SET NOCOUNT ON;

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]


END

GO

Output

TableName  | RowCount
------------------------------
tableName1  | 98
tableName2  | 0
tableName3  | 9
Labels:

Comments

Post a Comment

Popular posts from this blog

Cannot access a disposed object. A common cause of this error is disposing a context that was

  Cannot access a disposed object. A common cause of this error is disposing a context that was resolved from dependency injection and then later trying to use the same context instance elsewhere in your application. This may occur if you are calling Dispose() on the context, or wrapping the context in a using statement. If you are using dependency injection, you should let the dependency injection container take care of disposing context instances. Object name: 'PropertyContext'.  public class CustomerHub : Hub     {         public readonly ICustomerService _customerService;         private readonly IServiceScopeFactory _serviceScopeFactory;         public CustomerHub(ICustomerService service, IServiceScopeFactory serviceScopeFactory)         {             _customerService = service;             _serviceScopeFactory = serviceSco...
Post a Comment
Read more

First J2ME Application & Deployment(.jar,.jad) for your Supported Mobile Phones

Most probably 2 years ago I had decided to swap from C#.NET to Java.But at the time of its beginning I found it so messy than that of C#.As I desired to develop applications for my famous China Phone(Network down most of the time but supports java) at last I did it at the end of 2010.Now I'll discuss how to prepare your desktop to develop your first  j2me applications .   What is actually J2ME? J2ME stands for Java 2 Micro Edition, also known as Java ME. J2ME is a stripped down version of Java targeting devices with lower processing capabilities, like mobile phones, pagers .J2ME runs on a highly optimized version of Java Run-time Environment called KVM which comes pre-installed on devices(mobile phone,hand-held devices) supporting J2ME apps. Basic Concepts on J2ME: We can divide J2ME framework into three main parts namely Configurations, profiles and optional packages Configurations : A configuration contains the KVM and important class libraries, It defin...
Post a Comment
Read more