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

Referenced assembly does not have a strong name

  Steps to create strong named assembly Step 1 : Run visual studio command prompt and go to directory where your DLL located.   For Example my DLL located in  D:/hiren/Test.dll Step 2 : Now create  il file using below command.    D:/hiren> ildasm /all /out=Test.il Test.dll   (this command generate code library) Step 3 : Generate new Key for sign your project.    D:/hiren> sn -k mykey.snk Step 4 : Now sign your library using ilasm command.    D:/hiren> ilasm /dll /key=mykey.snk Test.il so after this step your assembly contains strong name and signed. Jjust add reference this new assembly in your project and compile project its running now. codeproject.com/Tips/341645/Referenced-assembly-does-not-have-a-strong-name
Post a Comment
Read more

SQL Server(from 2012) FileTable Features some useful SQL Statements

The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata.( FileTable ) Here is sequential SQL statements to enable,alter existing database for FileStream and create FileTable --check current config value EXEC sp_configure filestream_access_level; --reconfig GO EXEC sp_configure 'filestream access level',   2 Go RECONFIGURE GO -- chack file table enabled databases  SELECT DB_NAME(database_id) as [Database Name], non_transacted_access, non_transacted_access_desc ,directory_name FROM sys.database_filestream_options --  Enable a file table in an existing Database ALTER DATABASE TestDB  SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECT...
Post a Comment
Read more

update your package-lock.json according to what you have specified in the package.json file

  The objective of the   npm update   command is to update your   package-lock.json   according to what you have specified in the   package.json   file. This is the normal behavior. If you want to update your package.json file, you can use  npm-check-updates :  npm install -g npm-check-updates . You can then use these commands: ncu  Checks for updates from the package.json file ncu -u  Update the package.json file npm update --save  Update your package-lock.json file from the package.json file
Post a Comment
Read more