1

Please login or register to vote for this query.

(click on this box to dismiss)

The query is not ready yet.

Q&A for linguists, teachers, and students wanting to discuss the finer points of the Latin language

DECLARE @TagQuery VARCHAR(max) = '
  SELECT
    ''%DBNAME%'' AS DbName,
    Tag,
    CAST(Count AS FLOAT)/CAST(SynN+1 AS FLOAT) AS TagCount
  FROM (
    SELECT
      Tags.TagName AS Tag,
      MIN(Tags.Count) AS Count,
      COUNT(TagSynonyms.SourceTagName) AS SynN
    FROM [%DBNAME%].dbo.Tags
    LEFT JOIN [%DBNAME%].dbo.TagSynonyms
      ON Tags.TagName = TagSynonyms.TargetTagName
    GROUP BY Tags.TagName

    UNION

    SELECT
      TagSynonyms.SourceTagName AS Tag,
      Tags.Count,
      COUNT(*) OVER (PARTITION BY TagSynonyms.TargetTagName) AS SynN
    FROM [%DBNAME%].dbo.TagSynonyms, [%DBNAME%].dbo.Tags
    WHERE Tags.TagName = TagSynonyms.TargetTagName
  ) AllTags
'

DECLARE @output TABLE (
  DbName VARCHAR(max),
  Tag NVARCHAR(35),
  TagCount INT);

DECLARE @query VARCHAR(MAX);

DECLARE @DbTable TABLE (name VARCHAR(max));

INSERT INTO @DbTable
SELECT name
FROM sysdatabases
WHERE (name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND NOT name LIKE '%.Meta') OR name = 'StackExchange.Meta';

DECLARE dbcursor CURSOR FOR SELECT * FROM @DbTable;

DECLARE @dbname VARCHAR(max);
DECLARE @dbTagQuery VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @dbTagQuery = REPLACE(@TagQuery, '%DBNAME%', @dbname);
  INSERT INTO @output EXEC (@dbTagQuery);

  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

DECLARE simcursor CURSOR FOR
SELECT TOP 10 *
FROM @DbTable A, @DbTable B
WHERE A.Name < B.Name

fork query

edited mar 31 17

created mar 31 17 peterh