sp_MSforeachdb: only include results from databases with results
Well, first, stop using sp_MSforEachDb. Oh, the problems (if you want proof, see here).
How about:
DECLARE @cmd NVARCHAR(MAX) = N'', @sql NVARCHAR(MAX) = N'';
SELECT @cmd += N'IF EXISTS (SELECT 1 FROM '
+ QUOTENAME(name) + '.sys.tables WHERE name = N''Tabs'')
SET @sql += N''UNION ALL
SELECT ''''' + name + ''''',T.TabName
FROM ' + QUOTENAME(name) + '.dbo.Tabs AS T
WHERE EXISTS
(
SELECT 1 FROM ' + QUOTENAME(name) + '.dbo.TabModules AS TM
WHERE TM.TabID = T.TabID
AND TM.mID IN -- this should probably be exists too
(
...
)
)
'''
FROM sys.databases
WHERE state = 0 -- assume you only want online databases
AND database_id > 4; -- assume you don't want system dbs
EXEC sp_executesql @cmd, N'@sql NVARCHAR(MAX) OUTPUT', @sql OUTPUT;
SET @sql = STUFF(@sql, 1, 10, '') + N' ORDER BY TabName;';
PRINT @sql; -- this will appear truncated, but trust me, it is not truncated
-- EXEC sp_executesql @sql;
If you really want some unknown, arbitrary number of separate resultsets, the change is simple.
DECLARE @cmd NVARCHAR(MAX) = N'', @sql NVARCHAR(MAX) = N'';
SELECT @cmd += N'IF EXISTS (SELECT 1 FROM '
+ QUOTENAME(name) + '.sys.tables WHERE name = N''Tabs'')
SET @sql += N''SELECT ''''' + name + ''''',T.TabName
FROM ' + QUOTENAME(name) + '.dbo.Tabs AS T
WHERE EXISTS
(
SELECT 1 FROM ' + QUOTENAME(name) + '.dbo.TabModules AS TM
WHERE TM.TabID = T.TabID
AND TM.mID IN -- this should probably be exists too
(
...
)
)
ORDER BY T.TabName;
'';'
FROM sys.databases
WHERE state = 0 -- assume you only want online databases
--AND database_id > 4; -- assume you don't want system dbs
EXEC sp_executesql @cmd, N'@sql NVARCHAR(MAX) OUTPUT', @sql OUTPUT;
PRINT @sql; -- this will appear truncated, but trust me, it is not truncated
-- EXEC sp_executesql @sql;
Looping through databases with sp_MSforeachdb and returning 1 data set
David,
Is this what you want?
CREATE TABLE tempdb.dbo.Results (c1 VARCHAR(8000))
Declare @FullStatement varchar(MAX)
Set @FullStatement = 'SELECT ''use [?]''; SELECT dp.state_desc + N'' '' + dp.permission_name + N'' TO '' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500)) AS TSQLScript
FROM [?].sys.database_permissions AS dp
INNER JOIN [?].sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
and dpl.name not like ''##%'' -- excluds PBM accounts
and dpl.name not in (''dbo'', ''sa'', ''public'')
ORDER BY dp.permission_name ASC, dp.state_desc ASC'
INSERT INTO tempdb.dbo.Results Exec sp_MSforeachdb @FullStatement
select * FROM tempdb.dbo.Results
SQL Server 2014 sp_msforeachdb gives different results than running against individual database
Sp_msforeachdb is basically a global cursor that gives you access to the each database in turn by referencing [?]. It doesn't execute your command on each db by default. You can see this if you run a simple
EXEC sp_msforeachdb 'select db_name()'
For your first example, you're getting the same views because you're running the command against the same database every time. When you switch to [?].sys.sql_modules you start querying the sys.sql_modules in that database referenced by [?].
The problem with NULLs can be seen by running something like this:
SELECT OBJECT_NAME(object_id), definition FROM [msdb].[sys].[sql_modules] WHERE definition LIKE '%name%'
Run it in MSDB and you'll have a column name full of object names and a column with definitions. Run it in Master and the object names are now NULL even though you have the definitions. OBJECT_NAME() runs in the context of the current database, so you get NULLs unless you happen to have an object_id that matches, but then you're displaying the wrong object name. Definitions is directly referencing the database you want, so you still get them.
To get your query to work as you want it you just need to USE [?] (I'm looking for a definition like %name% because I know it will be there for testing)
CREATE TABLE [dbo].[#TMP](
[DBNAME] NVARCHAR(256) NULL,
[NAME] NVARCHAR(256) NOT NULL,
[DESC] NVARCHAR(MAX) NOT NULL);
DECLARE @command varchar(1000)
SELECT @command = 'USE [?]; INSERT INTO #TMP SELECT ''?'' as DBName, OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE ''%name%'''
EXEC sp_msforeachdb @command
SELECT * FROM #TMP
Select query from all databases where a table exists
You can still get to the results tab, even when it errors
Running a large T-SQL query on all databases
I finally got it working using cursors (which some people seem to hate, but it works). You have to use two single quotes in place of a single quote in the SQL query.
DECLARE @db_name AS nvarchar(max)
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN('master', 'model', 'msdb', 'tempdb')
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
EXEC('
USE ' + @db_name + '
SELECT
ServerName = @@SERVERNAME,
DatabaseName = DB_NAME(),
LoginName = AccessSummary.LoginName,
LoginType = CASE WHEN syslogins.isntuser = 1 THEN ''WINDOWS_LOGIN'' WHEN syslogins.isntgroup = 1 THEN ''WINDOWS_GROUP'' ELSE ''SQL_USER'' END,
SelectAccess = MAX(AccessSummary.SelectAccess),
InsertAccess = MAX(AccessSummary.InsertAccess),
UpdateAccess = MAX(AccessSummary.UpdateAccess),
DeleteAccess = MAX(AccessSummary.DeleteAccess),
DBOAccess = MAX(AccessSummary.DBOAccess),
SysadminAccess = MAX(AccessSummary.SysadminAccess)
FROM
(
/* Get logins with permissions */
SELECT
LoginName = sysDatabasePrincipal.name,
SelectAccess = CASE WHEN permission_name = ''SELECT'' THEN 1 ELSE 0 END,
InsertAccess = CASE WHEN permission_name = ''INSERT'' THEN 1 ELSE 0 END,
UpdateAccess = CASE WHEN permission_name = ''UPDATE'' THEN 1 ELSE 0 END,
DeleteAccess = CASE WHEN permission_name = ''DELETE'' THEN 1 ELSE 0 END,
DBOAccess = 0,
SysadminAccess = 0
FROM sys.database_permissions AS sysDatabasePermission
INNER JOIN sys.database_principals AS sysDatabasePrincipal
ON sysDatabasePrincipal.principal_id = sysDatabasePermission.grantee_principal_id
INNER JOIN sys.server_principals AS sysServerPrincipal
ON sysServerPrincipal.sid = sysDatabasePrincipal.sid
WHERE sysDatabasePermission.class_desc = ''OBJECT_OR_COLUMN''
AND sysDatabasePrincipal.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_USER'')
AND sysServerPrincipal.is_disabled = 0
UNION ALL
/* Get group members with permissions */
SELECT
LoginName = sysDatabasePrincipalMember.name,
SelectAccess = CASE WHEN permission_name = ''SELECT'' THEN 1 ELSE 0 END,
InsertAccess = CASE WHEN permission_name = ''INSERT'' THEN 1 ELSE 0 END,
UpdateAccess = CASE WHEN permission_name = ''UPDATE'' THEN 1 ELSE 0 END,
DeleteAccess = CASE WHEN permission_name = ''DELETE'' THEN 1 ELSE 0 END,
DBOAccess = 0,
SysadminAccess = 0
FROM sys.database_permissions AS sysDatabasePermission
INNER JOIN sys.database_principals AS sysDatabasePrincipalRole
ON sysDatabasePrincipalRole.principal_id = sysDatabasePermission.grantee_principal_id
INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
INNER JOIN sys.server_principals AS sysServerPrincipal
ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
WHERE sysDatabasePermission.class_desc = ''OBJECT_OR_COLUMN''
AND sysDatabasePrincipalRole.type_desc = ''DATABASE_ROLE''
AND sysDatabasePrincipalRole.name <> ''public''
AND sysDatabasePrincipalMember.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_USER'')
AND sysServerPrincipal.is_disabled = 0
UNION ALL
/* Get users in db_owner, db_datareader and db_datawriter */
SELECT
LoginName = sysServerPrincipal.name,
SelectAccess = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datareader'') THEN 1 ELSE 0 END,
InsertAccess = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datawriter'') THEN 1 ELSE 0 END,
UpdateAccess = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datawriter'') THEN 1 ELSE 0 END,
DeleteAccess = CASE WHEN sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datawriter'') THEN 1 ELSE 0 END,
DBOAccess = CASE WHEN sysDatabasePrincipalRole.name = ''db_owner'' THEN 1 ELSE 0 END,
SysadminAccess = 0
FROM sys.database_principals AS sysDatabasePrincipalRole
INNER JOIN sys.database_role_members AS sysDatabaseRoleMember
ON sysDatabaseRoleMember.role_principal_id = sysDatabasePrincipalRole.principal_id
INNER JOIN sys.database_principals AS sysDatabasePrincipalMember
ON sysDatabasePrincipalMember.principal_id = sysDatabaseRoleMember.member_principal_id
INNER JOIN sys.server_principals AS sysServerPrincipal
ON sysServerPrincipal.sid = sysDatabasePrincipalMember.sid
WHERE sysDatabasePrincipalRole.name IN (''db_owner'', ''db_datareader'', ''db_datawriter'')
AND sysServerPrincipal.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_LOGIN'')
AND sysServerPrincipal.is_disabled = 0
UNION ALL
/* Get users in sysadmin */
SELECT
LoginName = sysServerPrincipalMember.name,
SelectAccess = 1,
InsertAccess = 1,
UpdateAccess = 1,
DeleteAccess = 1,
DBOAccess = 0,
SysadminAccess = 1
FROM sys.server_principals AS sysServerPrincipalRole
INNER JOIN sys.server_role_members AS sysServerRoleMember
ON sysServerRoleMember.role_principal_id = sysServerPrincipalRole.principal_id
INNER JOIN sys.server_principals AS sysServerPrincipalMember
ON sysServerPrincipalMember.principal_id = sysServerRoleMember.member_principal_id
WHERE sysServerPrincipalMember.type_desc IN (''WINDOWS_LOGIN'', ''WINDOWS_GROUP'', ''SQL_LOGIN'')
AND sysServerPrincipalMember.is_disabled = 0
) AS AccessSummary
INNER JOIN MASTER.dbo.syslogins AS syslogins
ON syslogins.loginname = AccessSummary.LoginName
WHERE AccessSummary.LoginName NOT IN (''NT SERVICE\MSSQLSERVER'', ''NT AUTHORITY\SYSTEM'', ''NT SERVICE\SQLSERVERAGENT'')
GROUP BY
AccessSummary.LoginName,
CASE WHEN syslogins.isntuser = 1 THEN ''WINDOWS_LOGIN'' WHEN syslogins.isntgroup = 1 THEN ''WINDOWS_GROUP'' ELSE ''SQL_USER'' END')
FETCH c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names
SQL Server Sp_msforeachdb query character llimitation
SQL Server Sp_msforeachdb query character without limitation
When we write a query than is running for every database in the instance of SQL Server, we use Sp_msforeachdb.
If your query is bigger than 2000 chars, the query cannot work because Sp_msforeachdb has a character limitation of 2000 chars.
The solution is to re-create MySp_myforeachdb. We use sp_helptext to see the definition of sp_MSforeachdb and sp_MSforeach_worker, and we change to a new character limitation of 262144 chars.
USE master;
GO
/*
* This is the worker proc for all of the "for each" type procs. Its function is to read the
* next replacement name from the cursor (which returns only a single name), plug it into the
* replacement locations for the commands, and execute them. It assumes the cursor "hCForEach***"
* has already been opened by its caller.
* worker_type is a parameter that indicates whether we call this for a database (1) or for a table (0)
*/
create proc dbo.sp_Myforeach_worker
@command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null, @worker_type int =1
as
create table #qtemp ( /* Temp command storage */
qnum int NOT NULL,
qchar nvarchar(max) COLLATE database_default NULL
)
set nocount on
declare @name nvarchar(517), @namelen int, @q1 nvarchar(max), @q2 nvarchar(max)
declare @q3 nvarchar(max), @q4 nvarchar(max), @q5 nvarchar(max)
declare @q6 nvarchar(max), @q7 nvarchar(max), @q8 nvarchar(max), @q9 nvarchar(max), @q10 nvarchar(max)
declare @cmd nvarchar(max), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(max)
declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)
declare @local_cursor cursor
if @worker_type=1
set @local_cursor = hCForEachDatabase
else
set @local_cursor = hCForEachTable
open @local_cursor
fetch @local_cursor into @name
/* Loop for each database */
while (@@fetch_status >= 0) begin
/* Initialize. */
/* save the original dbname */
select @namesave = @name
select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
while (@cmd is not null) begin /* Generate @q* for exec() */
/*
* Parse each @commandX into a single executable batch.
* Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
* We also may append @commandX's (signified by '++' as first letters of next @command).
*/
select @replacecharindex = charindex(@replacechar, @cmd)
while (@replacecharindex <> 0) begin
/* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */
/* if the name has not been single quoted in command, do not doulbe them */
/* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */
select @name = @namesave
select @namelen = datalength(@name)
declare @tempindex int
if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
/* if ? is inside of '', we need to double all the ' in name */
select @name = REPLACE(@name, N'''', N'''''')
end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
/* if ? is inside of [], we need to double all the ] in name */
select @name = REPLACE(@name, N']', N']]')
end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
/* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */
/* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
select @tempindex = charindex(N'].[', @name)
select @nametmp = substring(@name, 2, @tempindex-2 )
select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
select @nametmp = REPLACE(@nametmp, N']', N']]')
select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
/* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */
/* j.i.c., since we should not fall into this case */
/* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */
select @nametmp = substring(@name, 2, len(@name)-2 )
select @nametmp = REPLACE(@nametmp, N']', N']]')
select @name = N'[' + @nametmp + N']'
end
/* Get the new length */
select @namelen = datalength(@name)
/* start normal process */
if (datalength(@cmd) + @namelen - 1 > 262144) begin
/* Overflow; put preceding stuff into the temp table */
if (@useq > 9) begin
close @local_cursor
if @worker_type=1
deallocate hCForEachDatabase
else
deallocate hCForEachTable
RAISERROR(55555, 16, 1); -- N'sp_MSforeach_worker assert failed: command too long'
return 1
end
if (@replacecharindex < @namelen) begin
/* If this happened close to beginning, make sure expansion has enough room. */
/* In this case no trailing space can occur as the row ends with @name. */
select @nextcmd = substring(@cmd, 1, @replacecharindex)
select @cmd = substring(@cmd, @replacecharindex + 1, 262144)
select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
select @replacecharindex = charindex(@replacechar, @cmd)
insert #qtemp values (@useq, @nextcmd)
select @useq = @useq + 1
continue
end
/* Move the string down and stuff() in-place. */
/* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */
/* In this case, the char to be replaced is moved over by one. */
insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
select @cmd = N' ' + substring(@cmd, @replacecharindex, 262144)
select @replacecharindex = 2
end else begin
select @cmd = substring(@cmd, @replacecharindex, 262144)
select @replacecharindex = 1
end
select @useq = @useq + 1
end
select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
select @replacecharindex = charindex(@replacechar, @cmd)
end
/* Done replacing for current @cmd. Get the next one and see if it's to be appended. */
select @usecmd = @usecmd + 1
select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
insert #qtemp values (@useq, @cmd)
select @cmd = substring(@nextcmd, 3, 262144), @useq = @useq + 1
continue
end
/* Now exec() the generated @q*, and see if we had more commands to exec(). Continue even if errors. */
/* Null them first as the no-result-set case won't. */
select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
select @q1 = qchar from #qtemp where qnum = 1
select @q2 = qchar from #qtemp where qnum = 2
select @q3 = qchar from #qtemp where qnum = 3
select @q4 = qchar from #qtemp where qnum = 4
select @q5 = qchar from #qtemp where qnum = 5
select @q6 = qchar from #qtemp where qnum = 6
select @q7 = qchar from #qtemp where qnum = 7
select @q8 = qchar from #qtemp where qnum = 8
select @q9 = qchar from #qtemp where qnum = 9
select @q10 = qchar from #qtemp where qnum = 10
truncate table #qtemp
exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
select @cmd = @nextcmd, @useq = 1
end /* while @cmd is not null, generating @q* for exec() */
/* All commands done for this name. Go to next one. */
fetch @local_cursor into @name
end /* while FETCH_SUCCESS */
close @local_cursor
if @worker_type=1
deallocate hCForEachDatabase
else
deallocate hCForEachTable
return 0
GO
GO
CREATE PROC sp_Myforeachdb
@command1 nvarchar(max), @replacechar nchar(1) = N'?', @command2 nvarchar(max) = null, @command3 nvarchar(max) = null,
@precommand nvarchar(max) = null, @postcommand nvarchar(max) = null
AS
set deadlock_priority low
/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
/* Preprocessor won't replace within quotes so have to use str(). */
declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */
if (@precommand is not null)
exec(@precommand)
declare @origdb nvarchar(128)
select @origdb = db_name()
/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */
/* Create the select */
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = dbo.sp_Myforeach_worker @command1, @replacechar, @command2, @command3, 1
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
declare @tempdb nvarchar(258)
SELECT @tempdb = REPLACE(@origdb, N']', N']]')
exec (N'use ' + N'[' + @tempdb + N']')
return @retval
GO
Help with sp_msforeachdb -like queries
Just wrap the statement you want to execute in an IF NOT IN:
EXEC sp_msforeachdb "
IF '?' NOT IN ('DBs','to','exclude') BEGIN
EXEC sp_whatever_you_want_to
END
"
Related Topics
Retrieve Oracle Last Inserted Identity
How to Concatenate All Columns in a Select with SQL Server
How to Pivot Dynamically with Date as Column
Shredding Xml from Execution Plans
Some Sort of "Different Auto-Increment Indexes" Per a Primary Key Values
Why No Many-To-Many Relationships
Converting Int to Real in SQLite
How to Remove White Space Characters from a String in SQL Server
Appending Data to a MySQL Database Field That Already Has Data in It
Database Design Question - Categories/Subcategories
How to Count Decimal Places in SQL
Update Multiple Columns in a Trigger Function in Plpgsql
Combine Two Tables in Select (SQL Server 2008)
Select One Row Per Index Value with Max Column Value
How to Loop Through a Table Variable in T-Sql