CODEWITHSUNDEEP
sqlsql-server
PeterK
PeterK

Reputation: 111

SQL query on certain database names

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 );

Upvotes: 0

Views: 383

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12959

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 );

Upvotes: 2

Related Questions