SQLSERVERLEARNER

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.
[sql]
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
[/sql]

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)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”SELECT * FROM &”,
@replacechar = ”&”
‘
[/sql]

Query3:
TSQL Query to drop all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”DROP TABLE &”,
@replacechar = ”&”
‘
[/sql]

Query4:
TSQL Query to truncate all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”TRUNCATE TABLE &”,
@replacechar = ”&”
‘
[/sql]

Query5:
TSQL Query to update Statistics on all the tables in all the databases(non system)
[sql]
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
‘
[/sql]

Query5:
TSQL Query to enable change tracking on all the tables in all the databases(non system)
[sql]
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
‘
[/sql]

Read more about using sp_msforeachdb and
using sp_msforeachtable