looping through all databases to find dependencies (cursor)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 22, 2018 at 3:55 pm

  #2010701

  Since you are doing a USE database in the dynamic SQL you don't need to include the databasename in the naming of the tables.  This is how I would have coded the dynamic SQL and used it:
  
declare @tbl as  nvarchar(128)
  , @cmd as  nvarchar(max)
  , @SQLCmd as nvarchar(max)
  , @database as nvarchar(128)
  , @parm   nvarchar(max) = N'@inDatabase nvarchar(128), @inTbl nvarchar(128)';

  set @cmd = N'
use ~database~;

  SELECT DISTINCT
@inDatabase as tablename,
o.name as ObjectName,
CASE o.xtype
   WHEN ''FN'' THEN ''FN''
   WHEN ''U'' THEN ''Table''
   WHEN ''V'' THEN ''View''
   WHEN ''P'' THEN ''SPROC''
   ELSE o.xtype
END as ObjectType
FROM
sys.sql_modules c
INNER JOIN dbo.sysobjects o
  ON c.object_id = o.id
LEFT JOIN dbo.sysobjects p
  ON o.Parent_obj = p.id
WHERE
c.definition LIKE @inTbl;
';

  set @tbl = N'%SHIPMENTS_Claims%';

  --get list of databases
declare [database_cursor] cursor local for
select
[name]
from
[master].[sys].[databases]
order by
[name] asc;

  open [database_cursor];

  fetch next from [database_cursor]
into @database;

  while @@fetch_status = 0
begin
  set @SQLCmd = replace(@cmd,N'~databasename~',@database);

    exec sys.sp_executesql @stmt = @SQLCmd, @params = @parm, @inDatabase = @database, @inTbl = @tbl;

    fetch next from [database_cursor]
  into @database;

  end;
close [database_cursor];

  deallocate [database_cursor];