How to find holding Databases of a list of Store Procedures

• Shazme

  Ten Centuries

  Points: 1292

  More actions

  June 17, 2011 at 2:32 am

  #253152

  Hi Experts,

  I have been provided with a list of store procedures(names only) which are performing badly. These reside in about 20 different databases. what is the best way to get the Database names/Table names

  for all of these store procedures so far I can trace down easily rather than spending too much time on finding these details

  Thanks much

  ImI

• shen-dest

  SSCrazy

  Points: 2498

  More actions

  June 17, 2011 at 2:56 am

  #1340242

  we are using a view joing all user database sysobjects. it may be a start.

  Regards,
  MShenel

• winash

  SSChampion

  Points: 11446

  More actions

  June 17, 2011 at 3:26 am

  #1340253

  There are multiple varities of scripts available for this purpose - here's a sample:

  DECLARE @Sql nvarchar(max)= '';

  DECLARE @tab TABLE(DBName sysname NOT NULL, ObjectName sysname NOT NULL,

  ObjectType sysname NOT NULL);

  SELECT @Sql = @Sql + CASE WHEN LEN(@Sql) = 0 THEN '' ELSE ' UNION ALL 'END +

  CHAR(13) + 'SELECT ''['+name+']'', name COLLATE DATABASE_DEFAULT,

  type_desc FROM ['+name+'].sys.objects'

  FROM sys.databases WHERE state_desc = 'ONLINE'

  AND user_access_desc = 'MULTI_USER';

  PRINT @Sql;

  INSERT @tab(DBName,ObjectName,ObjectType)

  EXEC(@Sql);

  SELECT * FROM @tab WHERE ObjectName = 'PutYourObjectNameHere';

• Shazme

  Ten Centuries

  Points: 1292

  More actions

  June 17, 2011 at 4:18 am

  #1340284

  Thanks much. It really worked for me.Much appreciated.