Operations On All Tables In All Databases in SQL Server using sp_MSforeachdb and sp_MSforeachtable

Posted on : 21-12-2011 | By : Devi Prasad | In : SQL Server Basics, SQLServerPedia Syndication

Share:

2


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 other databases to ignore in the if clause.

Query1:
TSQL Query to find the total number of rows in all the tables in all the databases in sql server.

CREATE TABLE #Tables (
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255), 
unused VARCHAR(255) );

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable 
@command1 = ''INSERT INTO #Tables EXEC sp_spaceused ''''&'''''',
@replacechar = ''&''
'
SELECT SUM(rows) from #Tables
SELECT MAX(rows) from #Tables

The above query gives two resultsets first on is the sum of all the rows in all the databases.
Second on is the maximum number of rows in all the databases.
& 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.

In the above query:
– The insert clause is executed for each table in the database by sp_MSforeachtable.
– The sp_MSforeachtable clause is executed for each database by sp_MSforeachdb.

Query2:
TSQL Query to select all the records from all the tables in all the databases(non system)

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable 
@command1 = ''SELECT * FROM &'',
@replacechar = ''&''
'

Query3:
TSQL Query to drop all the tables in all the databases(non system)

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable 
@command1 = ''DROP TABLE &'',
@replacechar = ''&''
'

Query4:
TSQL Query to truncate all the tables in all the databases(non system)

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable 
@command1 = ''TRUNCATE  TABLE &'',
@replacechar = ''&''
'

Query5:
TSQL Query to update Statistics on all the tables in all the databases(non system)

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
BEGIN
PRINT ''Started Processing Database ?''
EXEC dbo.sp_MSforeachtable 
@command1 = ''
PRINT ''''Started Update Statistics on ?.&''''
UPDATE STATISTICS &'',
@replacechar = ''&''
END
'

Query5:
TSQL Query to enable change tracking on all the tables in all the databases(non system)

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
BEGIN
PRINT ''Started Processing Database ?''
EXEC dbo.sp_MSforeachtable 
@command1 = ''
PRINT ''''Started ENABLE CHANGE TRACKING on ?.&''''
ALTER TABLE &
ENABLE CHANGE_TRACKING'',
@replacechar = ''&''
END
'

Read more about using sp_msforeachdb and
using sp_msforeachtable

(Visited 168 times, 1 visits today)



References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (2)

Great query! I was working on something similiar but couldn’t get it to work.

Is there a way to include the database name in the temp table in query1?

Thanks,

Hi Mack,

Below query includes the database name in the temp table.So that you will find the list of all tables in all databases in temp table(#Tables) along with the database name.

--drop table #Tables
CREATE TABLE #Tables (
DatabaseName VARCHAR(255),
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255), 
unused VARCHAR(255) );

EXEC sp_MSforeachdb
@command1 = '
IF not exists(select 1 where ''?'' in (''master'',''model'',''msdb'',''tempdb''))
EXEC [?].dbo.sp_MSforeachtable 
@command1 = ''INSERT INTO #Tables(TableName,rows,reserved,data,index_size,unused) EXEC sp_spaceused ''''&'''''',
@replacechar = ''&''

UPDATE #Tables SET databasename = ''?'' WHERE databasename IS NULL
'
SELECT SUM(rows) from #Tables
SELECT MAX(rows) from #Tables

Write a comment



Tags: , , , , , , , , ,