CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2014
Power User
Power User

Reputation: 25

Get data from many databases - dynamic database

We are using SQL Server 2014 Enterprise with many databases. I have to execute query and get reports / data from every database with EXACT SAME Schema and database starts with Cab

When a new company is added in our ERP project a new database is created with exact schema starting with Cab and incremented number is assigned to it like:

Cab1
Cab2
Cab3
Cab5
Cab10

I can get the database names as:

SELECT name
FROM master.sys.databases
where [name] like 'Cab%' order by [name]

I have to create a Stored Procedure to get data from tables of every database.

How to do that using a Stored Procedure as the databases are created dynamically starting with Cab?

Upvotes: 0

Views: 141

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

A quick and easy dynamic SQL solution would be something like this:

DECLARE @Sql nvarchar(max);
SET @Sql  = STUFF((
    SELECT ' UNION ALL SELECT [ColumnsList], '''+ [name] + ''' As SourceDb FROM '+ QUOTENAME([name]) + '.[SchemaName].[TableName]' + char(10)
    FROM master.sys.databases
    WHERE [name] LIKE 'Cab%' 
    FOR XML PATH('')
), 1, 10, '');

--When dealing with dynamic SQL, print is your best friend...
PRINT @Sql

-- Once the @Sql is printed and you can see it looks OK, you can run it.
--EXEC(@Sql)

Notes:

  1. Use quotename to protect against "funny" chars in identifiers names.
  2. Replace [ColumnsList] with the actual list of columns you need.
  3. There's no need for loops of any kind, just a simple stuff + for xml to mimic string_agg (which was only introduced in 2017).
  4. I've thrown in the source database name as a "bonus", if you don't want it that's fine.
  5. The Order by clause in the query that generates the dynamic SQL is meaningless for the final query, so I've removed it.

Upvotes: 0

Iman Kazemi
Iman Kazemi

Reputation: 552

You can use EXEC(@Statement) or EXEC SP_EXECUTESQL if you have to pass parameters.

CREATE OR ALTER PROCEDURE dbo.GetDataFromAllDatabases
AS
BEGIN
    DECLARE @T TABLE (id INT NOT NULL IDENTITY(1, 1), dbName VARCHAR(256) NOT NULL)
    INSERT INTO @T
    SELECT NAME FROM MASTER.SYS.DATABASES WHERE [NAME] LIKE 'Cab%' ORDER BY [NAME]

    CREATE TABLE #AllData (......)

    DECLARE @Id INT, @DbName VARCHAR(128)
    SELECT @Id = MIN(Id) FROM @T
    WHILE @Id IS NOT NULL
    BEGIN
        SELECT @DbName = dbName FROM @T WHERE Id = @Id
        DECLARE @Statement NVARCHAR(MAX)
        SET @Statement = CONCAT(N'INSERT INTO #AllData (...) SELECT .... FROM ', @DbName, '.dbo.[TableName]')
        EXEC(@Statement);
        --YOU CAN USE BELOW LINE TOO IF YOU NEED TO PASS VARIABLE
        --EXEC SP_EXECUTESQL @Statement, '@Value INT', @Value = 128
        SET @Id = (SELECT MIN(Id) FROM @T WHERE Id > @Id)
    END
END

Upvotes: 1

Related Questions