April 06, 2013

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. Lets say somebody want 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 want to disable all the triggers of one database. Then you can use a Script like below.

USE AdventureWorks2012
GO

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 AdventureWorks2012
GO

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.

No comments:

Post a Comment