Skip to content
Permalink
dev
Switch branches/tags
Go to file
 
 
Cannot retrieve contributors at this time
IF OBJECT_ID('dbo.sp_ineachdb') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_ineachdb AS RETURN 0')
GO
ALTER PROCEDURE [dbo].[sp_ineachdb]
-- mssqltips.com/sqlservertip/5694/execute-a-command-in-the-context-of-each-database-in-sql-server--part-2/
@command nvarchar(max) = NULL,
@replace_character nchar(1) = N'?',
@print_dbname bit = 0,
@select_dbname bit = 0,
@print_command bit = 0,
@print_command_only bit = 0,
@suppress_quotename bit = 0, -- use with caution
@system_only bit = 0,
@user_only bit = 0,
@name_pattern nvarchar(300) = N'%',
@database_list nvarchar(max) = NULL,
@exclude_pattern nvarchar(300) = NULL,
@exclude_list nvarchar(max) = NULL,
@recovery_model_desc nvarchar(120) = NULL,
@compatibility_level tinyint = NULL,
@state_desc nvarchar(120) = N'ONLINE',
@is_read_only bit = 0,
@is_auto_close_on bit = NULL,
@is_auto_shrink_on bit = NULL,
@is_broker_enabled bit = NULL,
@user_access nvarchar(128) = NULL,
@Help BIT = 0,
@Version VARCHAR(30) = NULL OUTPUT,
@VersionDate DATETIME = NULL OUTPUT,
@VersionCheckMode BIT = 0
-- WITH EXECUTE AS OWNER – maybe not a great idea, depending on the security of your system
AS
BEGIN
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SELECT @Version = '8.09', @VersionDate = '20220408';
IF(@VersionCheckMode = 1)
BEGIN
RETURN;
END;
IF @Help = 1
BEGIN
PRINT '
/*
sp_ineachdb from http://FirstResponderKit.org
This script will execute a command against multiple databases.
To learn more, visit http://FirstResponderKit.org where you can download new
versions for free, watch training videos on how it works, get more info on
the findings, contribute your own code, and more.
Known limitations of this version:
- Only Microsoft-supported versions of SQL Server. Sorry, 2005 and 2000.
- Tastes awful with marmite.
Unknown limitations of this version:
- None. (If we knew them, they would be known. Duh.)
Changes - for the full list of improvements and fixes in this version, see:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/
MIT License
Copyright (c) 2021 Brent Ozar Unlimited
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
*/
';
RETURN -1;
END
DECLARE @exec nvarchar(150),
@sx nvarchar(18) = N'.sys.sp_executesql',
@db sysname,
@dbq sysname,
@cmd nvarchar(max),
@thisdb sysname,
@cr char(2) = CHAR(13) + CHAR(10),
@SQLVersion AS tinyint = (@@microsoftversion / 0x1000000) & 0xff, -- Stores the SQL Server Version Number(8(2000),9(2005),10(2008 & 2008R2),11(2012),12(2014),13(2016),14(2017),15(2019)
@ServerName AS sysname = CONVERT(sysname, SERVERPROPERTY('ServerName')), -- Stores the SQL Server Instance name.
@NoSpaces nvarchar(20) = N'%[^' + CHAR(9) + CHAR(32) + CHAR(10) + CHAR(13) + N']%'; --Pattern for PATINDEX
CREATE TABLE #ineachdb(id int, name nvarchar(512), is_distributor bit);
/*
-- first, let's limit to only DBs the caller is interested in
IF @database_list > N''
-- comma-separated list of potentially valid/invalid/quoted/unquoted names
BEGIN
;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n <= LEN(@database_list)),
names AS
(
SELECT name = LTRIM(RTRIM(PARSENAME(SUBSTRING(@database_list, n,
CHARINDEX(N',', @database_list + N',', n) - n), 1)))
FROM n
WHERE SUBSTRING(N',' + @database_list, n, 1) = N','
)
INSERT #ineachdb(id,name,is_distributor)
SELECT d.database_id, d.name, d.is_distributor
FROM sys.databases AS d
WHERE EXISTS (SELECT 1 FROM names WHERE name = d.name)
OPTION (MAXRECURSION 0);
END
ELSE
BEGIN
INSERT #ineachdb(id,name,is_distributor) SELECT database_id, name, is_distributor FROM sys.databases;
END
-- now delete any that have been explicitly excluded - exclude trumps include
IF @exclude_list > N''
-- comma-separated list of potentially valid/invalid/quoted/unquoted names
BEGIN
;WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n <= LEN(@exclude_list)),
names AS
(
SELECT name = LTRIM(RTRIM(PARSENAME(SUBSTRING(@exclude_list, n,
CHARINDEX(N',', @exclude_list + N',', n) - n), 1)))
FROM n
WHERE SUBSTRING(N',' + @exclude_list, n, 1) = N','
)
DELETE d
FROM #ineachdb AS d
INNER JOIN names
ON names.name = d.name
OPTION (MAXRECURSION 0);
END
*/
/*
@database_list and @exclude_list are are processed at the same time
1)Read the list searching for a comma or [
2)If we find a comma, save the name
3)If we find a [, we begin to accumulate the result until we reach closing ], (jumping over escaped ]]).
4)Finally, tabs, line breaks and spaces are removed from unquoted names
*/
WITH C
AS (SELECT V.SrcList
, CAST('' AS nvarchar(MAX)) AS Name
, V.DBList
, 0 AS InBracket
, 0 AS Quoted
FROM (VALUES ('In', @database_list + ','), ('Out', @exclude_list + ',')) AS V (SrcList, DBList)
UNION ALL
SELECT C.SrcList
-- , IIF(V.Found = '[', '', SUBSTRING(C.DBList, 1, V.Place - 1))/*remove initial [*/
, CASE WHEN V.Found = '[' THEN '' ELSE SUBSTRING(C.DBList, 1, V.Place - 1) END /*remove initial [*/
, STUFF(C.DBList, 1, V.Place, '')
-- , IIF(V.Found = '[', 1, 0)
,Case WHEN V.Found = '[' THEN 1 ELSE 0 END
, 0
FROM C
CROSS APPLY
( VALUES (PATINDEX('%[,[]%', C.DBList), SUBSTRING(C.DBList, PATINDEX('%[,[]%', C.DBList), 1))) AS V (Place, Found)
WHERE C.DBList > ''
AND C.InBracket = 0
UNION ALL
SELECT C.SrcList
-- , CONCAT(C.Name, SUBSTRING(C.DBList, 1, V.Place + W.DoubleBracket - 1)) /*Accumulates only one ] if escaped]] or none if end]*/
, ISNULL(C.Name,'') + ISNULL(SUBSTRING(C.DBList, 1, V.Place + W.DoubleBracket - 1),'') /*Accumulates only one ] if escaped]] or none if end]*/
, STUFF(C.DBList, 1, V.Place + W.DoubleBracket, '')
, W.DoubleBracket
, 1
FROM C
CROSS APPLY (VALUES (CHARINDEX(']', C.DBList))) AS V (Place)
-- CROSS APPLY (VALUES (IIF(SUBSTRING(C.DBList, V.Place + 1, 1) = ']', 1, 0))) AS W (DoubleBracket)
CROSS APPLY (VALUES (CASE WHEN SUBSTRING(C.DBList, V.Place + 1, 1) = ']' THEN 1 ELSE 0 END)) AS W (DoubleBracket)
WHERE C.DBList > ''
AND C.InBracket = 1)
, F
AS (SELECT C.SrcList
, CASE WHEN C.Quoted = 0 THEN
SUBSTRING(C.Name, PATINDEX(@NoSpaces, Name), DATALENGTH (Name)/2 - PATINDEX(@NoSpaces, Name) - PATINDEX(@NoSpaces, REVERSE(Name))+2)
ELSE C.Name END
AS name
FROM C
WHERE C.InBracket = 0
AND C.Name > '')
INSERT #ineachdb(id,name,is_distributor)
SELECT d.database_id
, d.name
, d.is_distributor
FROM sys.databases AS d
WHERE ( EXISTS (SELECT NULL FROM F WHERE F.name = d.name AND F.SrcList = 'In')
OR @database_list IS NULL)
AND NOT EXISTS (SELECT NULL FROM F WHERE F.name = d.name AND F.SrcList = 'Out')
OPTION (MAXRECURSION 0);
;
-- next, let's delete any that *don't* match various criteria passed in
DELETE dbs FROM #ineachdb AS dbs
WHERE (@system_only = 1 AND (id NOT IN (1,2,3,4) AND is_distributor <> 1))
OR (@user_only = 1 AND (id IN (1,2,3,4) OR is_distributor = 1))
OR name NOT LIKE @name_pattern
OR name LIKE @exclude_pattern
OR EXISTS
(
SELECT 1
FROM sys.databases AS d
WHERE d.database_id = dbs.id
AND NOT
(
recovery_model_desc = COALESCE(@recovery_model_desc, recovery_model_desc)
AND compatibility_level = COALESCE(@compatibility_level, compatibility_level)
AND is_read_only = COALESCE(@is_read_only, is_read_only)
AND is_auto_close_on = COALESCE(@is_auto_close_on, is_auto_close_on)
AND is_auto_shrink_on = COALESCE(@is_auto_shrink_on, is_auto_shrink_on)
AND is_broker_enabled = COALESCE(@is_broker_enabled, is_broker_enabled)
)
);
-- if a user access is specified, remove any that are NOT in that state
IF @user_access IN (N'SINGLE_USER', N'MULTI_USER', N'RESTRICTED_USER')
BEGIN
DELETE #ineachdb WHERE
CONVERT(nvarchar(128), DATABASEPROPERTYEX(name, 'UserAccess')) <> @user_access;
END
-- finally, remove any that are not *fully* online or we can't access
DELETE dbs FROM #ineachdb AS dbs
WHERE EXISTS
(
SELECT 1 FROM sys.databases
WHERE database_id = dbs.id
AND
(
@state_desc = N'ONLINE' AND
(
[state] & 992 <> 0 -- inaccessible
OR state_desc <> N'ONLINE' -- not online
OR HAS_DBACCESS(name) = 0 -- don't have access
OR DATABASEPROPERTYEX(name, 'Collation') IS NULL -- not fully online. See "status" here:
-- https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql
)
OR (@state_desc <> N'ONLINE' AND state_desc <> @state_desc)
)
);
-- from Andy Mallon / First Responders Kit. Make sure that if we're an
-- AG secondary, we skip any database where allow connections is off
IF @SQLVersion >= 11 AND 3 = (SELECT COUNT(*) FROM sys.all_objects WHERE name IN('availability_replicas','dm_hadr_availability_group_states','dm_hadr_database_replica_states'))
BEGIN
DELETE dbs FROM #ineachdb AS dbs
WHERE EXISTS
(
SELECT 1 FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_replicas AS ar
ON ar.replica_id = drs.replica_id
INNER JOIN sys.dm_hadr_availability_group_states ags
ON ags.group_id = ar.group_id
WHERE drs.database_id = dbs.id
AND ar.secondary_role_allow_connections = 0
AND ags.primary_replica <> @ServerName
);
END
-- Well, if we deleted them all...
IF NOT EXISTS (SELECT 1 FROM #ineachdb)
BEGIN
RAISERROR(N'No databases to process.', 1, 0);
RETURN;
END
-- ok, now, let's go through what we have left
DECLARE dbs CURSOR LOCAL FAST_FORWARD
FOR SELECT DB_NAME(id), QUOTENAME(DB_NAME(id))
FROM #ineachdb;
OPEN dbs;
FETCH NEXT FROM dbs INTO @db, @dbq;
DECLARE @msg1 nvarchar(512) = N'Could not run against %s : %s.',
@msg2 nvarchar(max);
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @thisdb = CASE WHEN @suppress_quotename = 1 THEN @db ELSE @dbq END;
SET @cmd = REPLACE(@command, @replace_character, REPLACE(@thisdb,'''',''''''));
BEGIN TRY
IF @print_dbname = 1
BEGIN
PRINT N'/* ' + @thisdb + N' */';
END
IF @select_dbname = 1
BEGIN
SELECT [ineachdb current database] = @thisdb;
END
IF 1 IN (@print_command, @print_command_only)
BEGIN
PRINT N'/* For ' + @thisdb + ': */' + @cr + @cr + @cmd + @cr + @cr;
END
IF COALESCE(@print_command_only,0) = 0
BEGIN
SET @exec = @dbq + @sx;
EXEC @exec @cmd;
END
END TRY
BEGIN CATCH
SET @msg2 = ERROR_MESSAGE();
RAISERROR(@msg1, 1, 0, @db, @msg2);
END CATCH
FETCH NEXT FROM dbs INTO @db, @dbq;
END
CLOSE dbs;
DEALLOCATE dbs;
END
GO