How to run a query on all Tables?

In some cases, people need to run some queries against all the databases in their SQL Server instance. Something very very useful for administrators. Let's say somebody wants to give read permission on all tables he can use this sp_MSforeachdb stored procedure.
But what I’m going to tell in this post is not about it. I’m going to talk about sp_MSforeachtable. Let’s say somebody wants to disable all the triggers of one database. Then you can use a Script like below.
USE AdventureWorks2012GO

EXEC
sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'GO

But this command will not run on system tables. This is how I confirmed it.
USE AdventureWorks2012GO

EXEC
sp_MSforeachtable 'PRINT ''?'''GO
You can see when you run this query on AdventureWorks database, it will print only user tables. Not on system table of that database.

Tags

  • SQL
  • Table
  • T-SQL
  • Trigger
  • TSQL
  • SQL Server
  • SQL Sever