{"id":85,"date":"2011-12-21T10:31:06","date_gmt":"2011-12-21T10:31:06","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/12\/21\/operations-on-all-tables-in-all-databases-in-sql-server-using-sp_msforeachdb-and-sp_msforeachtable\/"},"modified":"2011-12-21T10:31:06","modified_gmt":"2011-12-21T10:31:06","slug":"operations-on-all-tables-in-all-databases-in-sql-server-using-sp_msforeachdb-and-sp_msforeachtable","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/12\/21\/operations-on-all-tables-in-all-databases-in-sql-server-using-sp_msforeachdb-and-sp_msforeachtable\/","title":{"rendered":"Operations On All Tables In All Databases in SQL Server using sp_MSforeachdb and sp_MSforeachtable"},"content":{"rendered":"

Queries that are used to perform various operations on all the tables on all the databases can be simplified by using the Undocumented stored procedures sp_MSforeachdb and sp_MSforeachtable.<\/strong>
\nCombined use of both sp_MSforeachdb and sp_MSforeachtable will make the tasks simpler.<\/p>\n

Example tsql queries:
\nNote: The below queries ignores system databases like master,model etc. Add the other databases to ignore in the if clause.<\/p>\n

Query1:<\/em>
\nTSQL Query to find the total number of rows in all the tables in all the databases in sql server.<\/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

EXEC sp_MSforeachdb
\n@command1 = ‘
\nIF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
\nEXEC [?].dbo.sp_MSforeachtable
\n@command1 = ”INSERT INTO #Tables EXEC sp_spaceused ””&”””,
\n@replacechar = ”&”
\n‘
\nSELECT SUM(rows) from #Tables
\nSELECT MAX(rows) from #Tables
\n[\/sql]<\/p>\n

The above query gives two resultsets first on is the sum of all the rows in all the databases.
\nSecond on is the maximum number of rows in all the databases.
\n& is used as a replace charecter for sp_MSforeachtable, this is done as both the procedures sp_MSforeachdb and sp_MSforeahtable have ? as default value.<\/p>\n

In the above query:
\n – The insert clause is executed for each table in the database by sp_MSforeachtable.
\n – The sp_MSforeachtable clause is executed for each database by sp_MSforeachdb.<\/p>\n

Query2:<\/em>
\nTSQL Query to select all the records from all the tables in all the databases(non system)<\/strong>
\n[sql]
\nEXEC sp_MSforeachdb
\n@command1 = ‘
\nIF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
\nEXEC [?].dbo.sp_MSforeachtable
\n@command1 = ”SELECT * FROM &”,
\n@replacechar = ”&”
\n‘
\n[\/sql]<\/p>\n

Query3:<\/em>
\nTSQL Query to drop all the tables in all the databases(non system)<\/strong>
\n[sql]
\nEXEC sp_MSforeachdb
\n@command1 = ‘
\nIF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
\nEXEC [?].dbo.sp_MSforeachtable
\n@command1 = ”DROP TABLE &”,
\n@replacechar = ”&”
\n‘
\n[\/sql]<\/p>\n

Query4:<\/em>
\nTSQL Query to truncate all the tables in all the databases(non system)<\/strong>
\n[sql]
\nEXEC sp_MSforeachdb
\n@command1 = ‘
\nIF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
\nEXEC [?].dbo.sp_MSforeachtable
\n@command1 = ”TRUNCATE TABLE &”,
\n@replacechar = ”&”
\n‘
\n[\/sql]<\/p>\n

Query5:<\/em>
\nTSQL Query to update Statistics on all the tables in all the databases(non system)<\/strong>
\n[sql]
\nEXEC sp_MSforeachdb
\n@command1 = ‘
\nIF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
\nBEGIN
\nPRINT ”Started Processing Database ?”
\nEXEC dbo.sp_MSforeachtable
\n@command1 = ”
\nPRINT ””Started Update Statistics on ?.&””
\nUPDATE STATISTICS &”,
\n@replacechar = ”&”
\nEND
\n‘
\n[\/sql]<\/p>\n

Query5:<\/em>
\nTSQL Query to enable change tracking on all the tables in all the databases(non system)<\/strong>
\n[sql]
\nEXEC sp_MSforeachdb
\n@command1 = ‘
\nIF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
\nBEGIN
\nPRINT ”Started Processing Database ?”
\nEXEC dbo.sp_MSforeachtable
\n@command1 = ”
\nPRINT ””Started ENABLE CHANGE TRACKING on ?.&””
\nALTER TABLE &
\nENABLE CHANGE_TRACKING”,
\n@replacechar = ”&”
\nEND
\n‘
\n[\/sql]<\/p>\n

Read more about using sp_msforeachdb<\/a> and
\nusing sp_msforeachtable<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Queries that are used to perform various operations on all the tables on all the databases can be simplified by using the Undocumented stored procedures sp_MSforeachdb and sp_MSforeachtable. Combined use of both sp_MSforeachdb and sp_MSforeachtable will make the tasks simpler. Example tsql queries: Note: The below queries ignores system databases like master,model etc. Add the…<\/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":[440,441,442,443,444,445,446,447,448,449],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/85"}],"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=85"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}