Dynamic multi-site query interpolation (basic tag search)

1

Please login or register to vote for this query.

(click on this box to dismiss)

GIS

Q&A for cartographers, geographers and GIS professionals

-- query must match #Output schema

declare @topTags int = 3;
declare @template varchar(max) = '
  
  SELECT top {K} ''{dbname}'' AS dbName, 
    Tags.TagName AS tag, Tags.Count AS mag 
    FROM [{dbname}].dbo.Tags order by mag desc

';

-- create data schemas

create table #DbList (
  dbname varchar(max) 
);

create table #KeyMap (
  oldKey varchar(max),
  newKey varchar(max)
);

create TABLE #Queries (
  query varchar(max)
); 

create TABLE #Output (
  dbName varchar(256),
  tag varchar(256),
  mag int
  
); 

-- populate database table

INSERT INTO #Dblist (dbname)
SELECT top 4 name FROM sysdatabases
WHERE (name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND NOT name LIKE '%.Meta') OR name = 'StackExchange.Meta';

-- populate string substitutions

INSERT INTO #KeyMap (oldKey, newKey) VALUES 
  ('TEMPLATE', @template),
  ('{UserVariable1}', 'substitution1'),
  ('{UserVariable2}', 'substitution2'),
  ('{K}',str(@topTags)); 
 
-- interpolation function

GO
CREATE PROCEDURE #interpolate
  @target NVARCHAR(MAX)
AS 
BEGIN

  DECLARE @Old VARCHAR(max), @New VARCHAR(max);
  DECLARE @ProcessedString VARCHAR(MAX) = @target;

  DECLARE KeyValueCursor CURSOR FOR
  SELECT oldKey, newKey FROM #KeyMap;

  OPEN KeyValueCursor;

  FETCH NEXT FROM KeyValueCursor INTO @Old, @New;

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @ProcessedString = REPLACE(@ProcessedString, @Old, @New);
    FETCH NEXT FROM KeyValueCursor INTO @Old, @New;
  END;

  CLOSE KeyValueCursor;
  DEALLOCATE KeyValueCursor;

  SELECT @ProcessedString AS ProcessedString;

END;
go

-- iterate over #DbList and pop/push dbname into #KeyMap
-- accumulate interpolated @source/TEMPLATE into #Output

DECLARE dbcursor CURSOR FOR SELECT * FROM #DbList;

  declare @query varchar(max);
  declare @dbslot varchar(max);
  declare @source varchar(max);

  set @source = (select newKey from #KeyMap where oldKey = 'TEMPLATE');

OPEN dbcursor;

  FETCH NEXT FROM dbcursor INTO @dbslot;
  INSERT INTO #KeyMap (oldKey,newKey) select '{dbname}',@dbslot

WHILE @@FETCH_STATUS = 0
BEGIN

  -- interpolate target query from source

  INSERT INTO #Queries
  EXEC #interpolate @target = @source;

  -- retrieve and execute dynamic query

  set @query = (select query from #Queries);
  INSERT INTO #Output EXEC (@query);

  -- query cycle clean-up  
  
  DELETE top(1) FROM #Queries;
  DELETE FROM #KeyMap where newKey = @dbslot;
  
  -- load next cycle into map
  
  FETCH NEXT FROM dbcursor INTO @dbslot;
  INSERT INTO #KeyMap (oldKey,newKey) select '{dbname}',@dbslot
  
END

CLOSE dbcursor;

-- inspect results

select * from #Output

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results