April 06, 2013

Run query on all databases all tables at once

When I was writing previous post, I was thinking about single line statement to disable all the triggers in SQL Server instance. But after that I found a way to how to do this. Those queries may looks like confusing.

Below query will print all the names of tables in all the databases of SQL Server instance.

EXEC sp_MSforeachdb 'USE [?] EXEC sp_MSforeachtable ''PRINT ''''/'''''',N''/'' '

But the problem here is you have so many ‘ here. And if you want to disable all the triggers on all the databases you can use below query.

EXEC sp_MSforeachdb 'USE [?] EXEC sp_MSforeachtable ''ALTER TABLE / DISABLE TRIGGER ALL'',N''/'' '

This query will disable all the triggers in you SQL Server Instance. But this alter statement will run on temp database’s tables also. In master and msdb you don’t need to worry because all of their tables are System tables. But temp database’s tables are user tables. So that is also not a much worry.

Enjoy SQL Server !

No comments:

Post a Comment