Say you have code you want executed against every table on a SQL Server instance, you could use SQL Server’s inbuilt sp_MSForEachDB and sp_MSForEachTable. I’m not a big fan of them though because they are undocumented, so I’d always be concerned Microsoft might decide to kill it with any given patch or service pack update. (I know the likelihood of that is extremely low but I’m a risk adverse kinda guy)
I prefer to use the example below. It may not be the most efficient snippet of code available on the net but it’s good and simple and it’s not going anywhere unless I drop it.
SET NOCOUNT ON DECLARE @Database TABLE (DbName SYSNAME) DECLARE @DbName AS SYSNAME SET @DbName = '' INSERT INTO @Database (DbName) SELECT NAME FROM sys.databases WHERE NAME <> 'tempdb' AND state_desc = 'ONLINE' ORDER BY NAME ASC WHILE @DbName IS NOT NULL BEGIN SET @DbName = ( SELECT MIN(DbName) FROM @Database WHERE DbName > @DbName ) /* PUT CODE HERE EXAMPLE PRINT Database Name */ PRINT @DbName END