SHARE
TWEET

Untitled

SQLSoldier Jul 3rd, 2013 156 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @MaxID int,
  2.         @CurrID int,
  3.         @SQL nvarchar(100)
  4. Declare @DBs Table (DBID int identity(1, 1) not null primary key,
  5.                                         DBName sysname not null unique)
  6.  
  7. Set NoCount On;
  8.  
  9. Insert Into @DBs (DBName)
  10. Select name
  11. from sys.databases
  12. -- 0 = online
  13. Where state = 0
  14. -- Not a snapshot
  15. And source_database_id Is Null
  16. -- Not marked read-only
  17. And is_read_only = 0;
  18.  
  19. Select @MaxID = MAX(DBID),
  20.         @CurrID = 1
  21. From @DBs
  22.  
  23. While @CurrID <= @MaxID
  24.   Begin
  25.         Select @SQL = 'Use ' + QUOTENAME(DBName) + ';
  26.                 If OBJECT_ID(''dbo.MyProcedure'') Is Not Null
  27.                         Exec dbo.MyProcedure;'
  28.         From @DBs
  29.         Where DBID = @CurrID
  30.        
  31.         Exec sp_executesql @SQL;
  32.        
  33.         Set @CurrID = @CurrID + 1
  34.   End
  35.  
  36. Set NoCount Off;
RAW Paste Data
Top