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