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.
| Author |
Topic |
|
jcornell
Starting Member
11 Posts |
 Posted - 2012-10-25 : 11:54:03
|
Hello I have what is probably a very simple issue but I have already spent too much time and I am on a dead line. I am completing a migration this weekend and I want a complete list of all user mappings from sys.database_principals, to achieve this I have written this codeEXECUTE sp_msforeachdb 'SELECT * FROM sys.database_principals'This is great and returns lots of very valuable information, however because I am utilising the sp_msforeachdb procedure it does not tell me the db name so I just have hundreds of little grids with everything I need except the db name, which is critical.I have tried to revise the script to use db_name() and it returns the db name that the query is run against (in my case master). So what I need to do is somehow obtain the db_name from inside the loop and get it to return the name for each processed db.This was my attempt at getting the information, however it just returns the db that the query was run against  EXECUTE sp_msforeachdb 'SELECT db_name(); SELECT * FROM sys.database_principals'I have also noticed that if you use the db_name() function it just returns the name of the db the query was run against EXECUTE sp_msforeachdb 'SELECT db_name()'Does anyone know how I can obtain this data within my query?RegardsJames |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-25 : 12:11:06
|
| [code]EXECUTE sp_msforeachdb 'SELECT ''?'',* FROM sys.database_principals'[/code] |
 |
|
|
jcornell
Starting Member
11 Posts |
Posted - 2012-10-26 : 04:14:04
|
| Thank you for the new query that works perfectly, can you just elaborate on what the ''?'' is doing? Just so I know for future referenceJames |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-26 : 06:46:07
|
The question mark is a place-holder for the database name. So as the stored proc processes each database, the question mark will be replaced with the name of the database.So, for example, for the master database, the query will translate to SELECT 'master',* FROM sys.database_principals |
|
|
|
jcornell
Starting Member
11 Posts |
Posted - 2012-10-26 : 08:06:26
|
| Thank you for all of your help I really appreciate it |
|
|
|
|
|
|
sp_msforeachdb issue...