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 ([TableNa...