useing USE within UNION

is it possible to make the below syntax work?
USE DEC_LED
SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id) 
FROM sys.database_permissions dp JOIN sys.database_principals p 
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union
USE DEC_HED
SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id) 
FROM sys.database_permissions dp JOIN sys.database_principals p 
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union
USE DEC_TPE
SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id) 
FROM sys.database_permissions dp JOIN sys.database_principals p 
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'

Open in new window

LVL 5
25112Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>i would like that to be dynamic.. <<
Then you will have no choice, but to resort to dynamic SQL.
0
 
akku101Commented:
Check this one


SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id)
FROM sys.database_permissions dp JOIN sys.database_principals p
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union all

SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id)
FROM sys.database_permissions dp JOIN sys.database_principals p
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
union all

SELECT db_name(),p.name, permission_name, state_desc, object_name(major_id)
FROM sys.database_permissions dp JOIN sys.database_principals p
ON dp.grantee_principal_id = p.principal_id AND name = 'DECLA\DE-eDEC_Developers'
0
 
akku101Commented:
sorry above wont work as it referencing
 different databases. i missed database names.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
jogosCommented:
You can put the database name before the tablename

from FROM DEC_LED.sys.database_permissions
0
 
25112Author Commented:
then db_name() does not pick up automatically.. i would like that to be dynamic..
0
 
jogosConnect With a Mentor Commented:
db_name() indeed only returns the name of the current db.

Same you have to refere to a function in the actual db       dbname.schema.function . Making your own function can help.

You can also create a view in each database with the db-specific things like db_name() and maybe other stuff you want to.  Joining them in the db_name() is then that off the database where the view is.
0
 
25112Author Commented:
ok-
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.