October 24, 2011 at 1:20 pm
Hello All,
This might be a very simple for some but I am in a bit pickle for this.
i have run on each database and gives me list of all the non-default schema's and their owners.
I want to be able to run this script on every database in a server by reading the name of the databases from sys table.
I tired to use undocumented Sp MSforeachdb but i am stuck, can someone please guide me.
............................This is the script i got form google ...................................
USE <<database_name>> — Execute for each database
GO
– List of non-standard schemas and their owners
SELECT a.name AS Database_Schema_Name, b.name AS Schema_Owner
FROM sys.schemas a
INNER JOIN sys.database_principals b
ON a.principal_id = b.principal_id
WHERE a.schema_id <> a.principal_id
AND b.type <> ‘R’
– List of users and their default schemas
SELECT name AS Database_User, Default_Schema_Name
FROM sys.database_principals
WHERE type <> ‘R’
Thanks in advance
October 25, 2011 at 7:17 am
wow, really no one??
October 25, 2011 at 7:20 am
What's wrong with msforeachdb?
October 26, 2011 at 2:34 am
Ninja's_RGR'us (10/25/2011)
What's wrong with msforeachdb?
Well, something, actually.
You could use Aaron Bertrand's replacement[/url] or my humble attempt[/url].
-- Gianluca Sartori
October 26, 2011 at 2:42 am
Just ran a slightly modified version of your code with sp_msforeachdb and it works. Here is the code that I've used. If you still have problems with sp_msforeachdb can you let us know what the problem is?
exec sp_MSforeachdb
'SELECT ''?'' as DBName, a.name AS Database_Schema_Name, b.name AS Schema_Owner
FROM ?.sys.schemas a
INNER JOIN ?.sys.database_principals b
ON a.principal_id = b.principal_id
WHERE a.schema_id <> a.principal_id
AND b.type <> ''R''
--- List of users and their default schemas
SELECT ''?'' as DBName, name AS Database_User, Default_Schema_Name
FROM ?.sys.database_principals
WHERE type <> ''R'''
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 26, 2011 at 2:49 am
How about this?
DECLARE @sql AS VARCHAR(MAX)
SELECT @sql = COALESCE(@SQL + '; ', '') + sql_command
FROM (SELECT 'USE ' + QUOTENAME(name) +
' SELECT a.name AS Database_Schema_Name, b.name AS Schema_Owner
FROM sys.schemas a
INNER JOIN sys.database_principals b
ON a.principal_id = b.principal_id
WHERE a.schema_id <> a.principal_id
AND b.type <> ''R'';
SELECT name AS Database_User, Default_Schema_Name
FROM sys.database_principals
WHERE type <> ''R''' AS sql_command
FROM sys.databases
) a
EXEC(@SQL)
November 2, 2011 at 12:59 am
BEGIN
SET NOCOUNT ON
DECLARE
@RowIndex INT
,@RowCount INT
,@Name VARCHAR(300)
,@SQLQuery VARCHAR(MAX)
IF NOT OBJECT_ID('tempdb..#tempCheckDatabase') IS NULL
BEGIN
DROP TABLE #tempCheckDatabase
END
SELECT
Identity(INT ,1 ,1) AS RowID
,NAME AS DBName INTO #tempCheckDatabase
FROM sys.databases WITH (NOLOCK)
SELECT
@RowCount = @@RowCount
,@RowIndex = 1
WHILE @RowIndex <= @RowCount
BEGIN
SELECT @Name = DBName
FROM #tempCheckDatabase WITH (NOLOCK)
WHERE RowID = @RowIndex
SELECT @sqlquery = 'USE ' + @Name +';
SELECT ''/************************BEGIN***********************/''
DECLARE @DatabaseName VARCHAR(500)
SELECT @DatabaseName = ''' + @Name + '''
SELECT @DatabaseName AS [DatabaseName]
SELECT
a.name AS Database_Schema_Name
,b.name AS Schema_Owner
FROM
sys.schemas a
INNER JOIN sys.database_principals b
ON a.principal_id = b.principal_id
WHERE
a.schema_id <> a.principal_id
AND b.type <> ''R'';
SELECT
name AS Database_User
,Default_Schema_Name
FROM sys.database_principals
WHERE type <> ''R'';
SELECT ''/*************************END************************/'''
PRINT @sqlquery
EXEC (@SQLQuery)
SELECT @RowIndex = @RowIndex + 1 , @sqlquery = ''
END
SET NOCOUNT OFF
END
GO
Hope your issue has been sorted, or else try this.
Patel Mohamad
September 17, 2014 at 10:45 am
Occasionally there will be an issue with the actual name of the database - for example, it will contain a space like "My Database" In this case
Example:
EXEC sp_MSforeachdbCleanPasswords 'USE [?];'
OR
EXEC sp_MSforeachdbCleanPasswords 'USE ''[?]'';'
depending on the need for the dynamic sql.
Jamie
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply