SQL query on certain database names

Question

I currently have many databases on a server. I want to run a query on only databases that end in "AccountsLive".

Not all of them end with this, so I kind of want to do a wildcard %AccountsLive query and not using a WHERE NOT name IN('master', 'tempdb', 'model', 'msdb')

Is this possible?

Below is the code I currently have:

DECLARE @Sql NVARCHAR(MAX) = NULL;

SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) + 'SELECT * FROM ' + QUOTENAME([name]) + '.SL_TRANSACTIONS'
FROM    sys.databases
WHERE   not [name] in ('master', 'tempdb', 'model', 'msdb');


EXECUTE ( @Sql );

Did you try WHERE [name] LIKE '%AccountsLive' ?

Chetan
– Chetan

2021-03-08 03:20:21 +00:00
Commented Mar 8, 2021 at 3:20 — Chetan
– Chetan, Commented Mar 8, 2021 at 3:20
no i didn't try it as i am an idiot!! thank you!!

PeterK
– PeterK

2021-03-08 05:49:20 +00:00
Commented Mar 8, 2021 at 5:49 — PeterK
– PeterK, Commented Mar 8, 2021 at 5:49

Venkataraman R · Accepted Answer · 2021-03-08 04:56:50Z

2

You can put list of table names in a table variable and query them accordingly. I have also added schema as dbo, to make the name as three part name, assuming the table in the dbo schema.

DECLARE @table table(dbname sysname)

INSERT INTO @table(dbname)
SELECT NAME FROm sys.databases where name like '%AccountsLive'

DECLARE @Sql NVARCHAR(MAX) = NULL;

SELECT  @Sql = COALESCE(@Sql + ' UNION ALL ' + CHAR(13) + CHAR(10), '' ) + 'SELECT * FROM ' + QUOTENAME(dbname) + '.dbo.SL_TRANSACTIONS'
FROM    @table


EXEC( @Sql );

answered Mar 8, 2021 at 4:56

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PeterK Over a year ago

actually, i am getting an error on this one:

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in UNION ALL operator.

any ideas?

Venkataraman R Over a year ago

@PeterK, can you please raise separate question. It seems some issue with different collation in different databases. Please put query and add separate question. It is different error.

PeterK Over a year ago

ah sorry, thought it may be linked to this - will do!

Venkataraman R Over a year ago

@PeterK, no problem. sure.

Collectives™ on Stack Overflow

SQL query on certain database names

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related