sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online.<\/p>\n
Use:<\/strong> Examples:<\/strong> Syntax:<\/strong> Note: <\/em> ? Can be used for table name in @command1,@command2 and @command3.<\/p>\n Example TSQL Queries:<\/strong> Query2:<\/em> Query3:<\/em> The above query executes below commands if you are using Adwentureworks. Query4:<\/em> EXEC sp_MSforeachtable ‘INSERT INTO #Tables EXEC sp_spaceused ”?”’ SELECT sum(rows) from #Tables The first resultset would be the detailed space used data for each table. Query5:<\/em> EXEC sp_MSforeachtable SELECT * FROM #Tables Query6:<\/em> Query7:<\/em> Query8:<\/em> Query9:<\/em> Query10:<\/em> Query11:<\/em> Query12:<\/em> sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online. Use: sp_MSforeachtable can be used to run a query against each table in the database. Examples: Updating statestics for each table in the database Logging the space used by each table in the database finding the number of rows for each…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41,3],"tags":[430,431,432,433,434,435,436,437,438,439],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/84"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nsp_MSforeachtable can be used to run a query against each table in the database.<\/em><\/p>\n
\nUpdating statestics for each table in the database
\nLogging the space used by each table in the database
\nfinding the number of rows for each table in database<\/p>\n
\n[sql]
\nEXEC sp_MSforeachtable @command1,
\n@replacechar,
\n@command2,
\n@command3,
\n@whereand,
\n@precommand,
\n@postcommand
\n[\/sql]
\n@command1 – Is the command that hs to be run against each table.
\n@replacechar – Is the replace charecter for table name. Default value is ?
\n@command2 – This is the second command that is run after @command1.
\n@command3 – This is the third command that is run after @command1 and @command2.
\n@whereand – This is the where clasue that can be used to filter tables.
\n@precommand – This command is run first by the procedure.It is run before processing any table
\n@postcommand – This command is run last by the procedure.It is run after processing all the tables.<\/p>\n
\n
Query1:<\/em>
\nQuery to list all the tables in the database<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘SELECT ”?”’
\n[\/sql]
\n? – Represents table name
\n”?” is used as the name of the table is a string and has to be opened and closed in quotes in select clause.<\/p>\n
\nQuery to save and list all the tables in the database:<\/strong>
\n[sql]
\nCREATE TABLE #Tables (TableName VARCHAR(255))
\nEXEC sp_MSforeachtable ‘INSERT INTO #Tables SELECT ”?”’
\nSELECT * FROM #Tables
\n[\/sql]
\nTable names are saved into the temporary table #Tables.
\nNote:<\/strong> There will be a chage in resultsets for query1 and query2.
\nYou can observe that you get multiple resultsets one for each table with query1 and a single resultset containing all table names for query2.<\/p>\n
\nQuery to update statistics of all the tables in the database.<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘UPDATE STATISTICS ?’
\n[\/sql]<\/p>\n
\n[sql]
\nUPDATE STATISTICS [Sales].[Store]
\nUPDATE STATISTICS [Production].[ProductPhoto]
\n…. etc
\n[\/sql]<\/p>\n
\nQuery to calculate the space used for each table.<\/strong>
\n[sql]
\nCREATE TABLE #Tables (
\nTableName VARCHAR(255),
\nrows int,
\nreserved VARCHAR(255),
\ndata VARCHAR(255),
\nindex_size VARCHAR(255),
\nunused VARCHAR(255) );<\/p>\n
\nSELECT * FROM #Tables<\/p>\n
\n[\/sql]<\/p>\n
\nThe second resultset will give the total number of rows in all the tables in the database.<\/p>\n
\nUsing where clause with sp_MSforeachtable(@whereand clause).<\/strong>
\nQuery to execute sp_spaceused procedure and fetch the resulset of all the tables in the database that begin with p.
\n[sql]
\n–drop table #Tables
\nCREATE TABLE #Tables (
\nTableName VARCHAR(255),
\nrows int,
\nreserved VARCHAR(255),
\ndata VARCHAR(255),
\nindex_size VARCHAR(255),
\nunused VARCHAR(255) );<\/p>\n
\n@command1 = ‘INSERT INTO #Tables EXEC sp_spaceused ”?”’,
\n@whereand = ‘ and object_name(object_id) like ”p%” ‘<\/p>\n
\n[\/sql]<\/p>\n
\nQuery to delete data in all the tables in the database.<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘delete ?’
\n[\/sql]<\/p>\n
\nQuery to disable triggers on all tables<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘
\nRAISERROR(”Started disabling triggers on table ?”,1,1)
\nALTER TABLE ? DISABLE TRIGGER ALL ‘
\n[\/sql]<\/p>\n
\nQuery to truncate all the tables in the database.<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘TRUNCATE TABLE ?’
\n[\/sql]<\/p>\n
\nQuery to disable constaints on all tables<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘
\nRAISERROR(”Started disabling constraints on table ?”,1,1)
\nALTER TABLE ? NOCHECK CONSTRAINT ALL’
\n[\/sql]<\/p>\n
\nQuery to enable change tracking on all the tables<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘
\nRAISERROR(”Started enabling change tracking on table ?”,1,1)
\nALTER TABLE ? ENABLE CHANGE_TRACKING;’
\n[\/sql]<\/p>\n
\nQuery to rebuild clustered index\/all non clusted indexes on all the tables<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘
\nRAISERROR(”Started rebuild index on table ?”,1,1)
\nALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE);’
\n[\/sql]<\/p>\n
\nQuery to drop all the tables in the database<\/strong>
\n[sql]
\nEXEC sp_MSforeachtable ‘
\nRAISERROR(”Started dropping table ?”,1,1)
\nDROP TABLE ?’
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"