Declaring databases as a variable using cursor? SQL Server / T-SQL-CodePudding

Currently my cursor results produces a @database_name VARCHAR, I am trying to figure out how to get that as a variable that I can use to loop a query through multiple databases. Most of what I can find cursor related is very much the same loop and print that I have.

I have been through so many different methods that this is probably far from my best attempt and I am starting to go backwards

DECLARE 
    @cursor_db CURSOR

DECLARE
    @database_id VARCHAR(10), 
    @database_name VARCHAR(255);

SET @cursor_db = CURSOR FOR 
        SELECT database_id, name
        FROM sys.databases
        WHERE name LIKE 'Company%';

OPEN @cursor_db;

FETCH NEXT FROM @cursor_db INTO @database_id, @database_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @database_id   ' '   @database_name

    SELECT * 
    FROM @database_name i
    WHERE sys.columns i LIKE '%Orders%'

    FETCH NEXT FROM @cursor_db INTO @database_id, @database_name;
END;

CLOSE @cursor_db;
DEALLOCATE @cursor_db;

CodePudding user response：

Try this:

DECLARE @DBNamePattern  varchar(50) =   'Company%' 
    ,   @ColNamePattern varchar(50) =   'Order%'
;

SELECT      
                        
                DatabaseName    =   C.Table_Catalog
            ,   DatabaseID      =   D.database_id
            ,   SchemaName      =   C.TABLE_SCHEMA
            ,   TableName       =   C.Table_Name
            ,   ColName         =   C.Column_Name

FROM        INFORMATION_SCHEMA.COLUMNS  C
JOIN        sys.databases               D   ON  D.Name = C.TABLE_CATALOG
WHERE   
        Table_Catalog   LIKE    @DBNamePattern
    AND Column_Name     LIKE    @ColNamePattern

ORDER BY    
            DatabaseName
        ,   TableName

CodePudding user response：

In case anyone was wondering the outcome you can use a concatenated string in a dynamic query

-- before cursor opens
DECLARE @base_query1 VARCHAR(max);
DECLARE @base_query2 VARCHAR(max);
DECLARE @query VARCHAR(max)

SET @base_query1 = 'SELECT COUNT(*) AS CNT FROM '
SET @base_query2 = '..TableName' -- expected table name

-- inside the loop
SET @query = CONCAT( @base_query1, @database_name, @base_query2 )    
    EXEC(@query);