Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My experiences and references in SQL server
Browse by Tags · View All
SQL Server 14
#SQLServer 14
SQL Scripts 13
#TSQL 6
TSQL 6
SQL Serevr - Issues and Resolutions 3
SQL Server - Best Practises 3
SQL server - Misconceptions 3
SQL server - Statistics 2
SQL Server - Wait stats and Queues 2

Archive · View All
October 2011 8
March 2011 7
April 2011 4
May 2011 3
November 2011 3
December 2010 3
December 2011 2
June 2008 2
February 2011 2
February 2012 1

SQLZealot's Blog

Presence of a procedure in multiple databases in the same server

Oct 14 2011 5:58AM by Latheesh NK   

I have come across many times in a situation that I need to check the existence of a procedure in multiple databases in a given server.

Hope, this would help you guys too...

Create Proc SQLZealot_FindProcInDBs (@ProcName Varchar(500)) 
As
BEGIN
 Create Table  #TempDBNames (ID int identity(1,1), Name Varchar(500))

 Declare @RowCount int, @Incrementor int, @DBName Varchar(500),@Qry Varchar(500)
 Set @Incrementor = 1
 

 Select @RowCount=count(1) From sys.databases  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and ISNULL(HAS_DBACCESS ([Name]),0)=1; 

 Insert Into #TempDBNames 
 Select name From sys.databases  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and ISNULL(HAS_DBACCESS ([Name]),0)=1; 

 While @Incrementor<=@RowCount
 Begin
  Select @DBName = Name From #TempDBNames Where ID = @Incrementor
  SET @Qry = 'Select DB_NAME(),* From ' + @DBName +'..sysobjects Where name='''+ @ProcName+''' and type=''P'' '
  Print (@Qry)
  Exec (@Qry)
  Set @Incrementor = @Incrementor + 1
  
 End

 Drop table #TempDBNames
 
END

/*
Usage:
SQLZealot_FindProcInDBs 'pr_swb_tt_updateTaskEfforts'
*/


Republished from SQL - My Best Friend [58 clicks].  Read the original version here [32134 clicks].

Latheesh NK
51 · 4% · 1178
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • -- I modifed your procedure @DBName and [] ALTER Proc uspSQLZealot_FindProcInDBs (@ProcName Varchar(500)) As BEGIN Create Table #TempDBNames (ID int identity(1,1), Name Varchar(500))

    Declare @RowCount int, @Incrementor int, @DBName Varchar(500),@Qry Varchar(500) Set @Incrementor = 1

    Select @RowCount=count(1) From sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and ISNULL(HAS_DBACCESS ([Name]),0)=1;

    Insert Into #TempDBNames Select name From sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') and ISNULL(HAS_DBACCESS ([Name]),0)=1;

    While @Incrementor<=@RowCount Begin Select @DBName = Name From #TempDBNames Where ID = @Incrementor SET @Qry = 'Select '''+ @DBName+ ''' AS DBName,* From [' + @DBName +']..sysobjects Where name='''+ @ProcName+''' and type=''P'' ' Print (@Qry) Exec (@Qry) Set @Incrementor = @Incrementor + 1

    End

    Drop table #TempDBNames

    END

    /* Usage: uspSQLZealotFindProcInDBs 'prswbttupdateTaskEfforts' */

    commented on Nov 1 2011 8:12AM
    Manigandan
    802 · 0% · 36
  • Well. I get your point. Please feel free to change as per your requirement.

    commented on Nov 15 2011 5:29AM
    Latheesh NK
    51 · 4% · 1178

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]