run the script through all the databases on the se

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

run the script through all the databases on the se

Author

Topic

deal11deal1
Starting Member

34 Posts

Posted - 2011-10-25 : 09:23:23

Hello All,

This might be a very simple for some but I am in a bit pickle for this.

i have run below script 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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-25 : 09:36:57

You could loop hrough and run the last two statements here for each database

declare @s varchar(max), @t varchar(max)
select @s = 'SELECT a.name AS Database_Schema_Name, b.name AS Schema_Owner
FROM <db>.sys.schemas a
INNER JOIN <db>.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 <db>.sys.database_principals
WHERE type <> ''R''
'

select @t = REPLACE(@s,'<DB>','mydb')
exec (@t)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-25 : 09:42:18

in fact

declare @s varchar(max), @t varchar(max)
select @s = 'SELECT db=''<DB>'',a.name AS Database_Schema_Name, b.name AS Schema_Owner
FROM <db>.sys.schemas a
INNER JOIN <db>.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 db=''<DB>'',name AS Database_User, Default_Schema_Name
FROM <db>.sys.database_principals
WHERE type <> ''R''
'

declare @db varchar(1000)
select @db = ''
while @db < (select MAX(name) from master..sysdatabases)
begin
select @db = MIN(name) from master..sysdatabases where @db < name

select @t = REPLACE(@s,'<DB>',@db)
exec (@t)
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

deal11deal1
Starting Member

34 Posts

Posted - 2011-10-25 : 10:45:18

Thank you so very much , i will give it a try .

deal11deal1
Starting Member

34 Posts

Posted - 2011-10-26 : 17:08:13

it works like a charm, Thanks again.

can i please ask if you or any one else can explain to me how this works..... hope its not too much trouble.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2011-10-27 : 00:09:06

Maybe u can try sp_msforeachdb