URGENT!!! Please assist with script that checks for all the objects owned by a user in all the database on a server
-
This code checks for all the objects owned by a user in all the database on a server. This code works fine but i cant seem to select the database name in the query, so that i can see in which database an object lies
EXEC sp_Msforeachdb @command1 = -- Looks through all databases on server
"Select o.name,u.name,o.uid
from ?..sysusers u,?..sysobjects o
where (u.name like '%moh60108'
and o.uid = u.uid)"
WHEN I DO ADD A FIELD IN THE SELECT STATEMENT TO SELECT THE DATABASE NAME I GET AN ERROR
EXEC sp_Msforeachdb @command1 = -- Looks through all databases on server
"Select o.name,u.name,o.uid,d.name
from ?..sysusers u,?..sysobjects o, ?..sysdatabases d
where (u.name like '%moh60108'
and o.uid = u.uid)"
ERROR:
Server: Msg 103, Level 15, State 7, Line 2
The identifier that starts with 'Select o.name,u.name,o.uid,d.name
from ?..sysusers u,?..sysobjects o, ?..sysdatabases d
where (u.name like '%moh60108'
and ' is too long. Maximum length is 128.
is their away around this or a way to increase the length
-
Hi,
May I ask why are you using an undocumented procedure? Why not just write your own loop?
I don't think your query is right, since you are not joining to sysdatabases.
Have you tried doing it the following way?
Declare
@cmd varchar(1000)
Select
@cmd = 'Select o.name,u.name,o.uid ,
from ?..sysusers u,?..sysobjects o
where (u.name like ''%moh60108''
and o.uid = u.uid)'
EXEC
sp_Msforeachdb @command1 = @cmd
I think that solves the length issue. But you still need to be sure that your query is correct.
I hope that helps.
-
One more thing
If you just want to add the database name the following will work:
EXEC
sp_Msforeachdb @command1 =
"Select o.name,u.name,'?'
from ?..sysusers u
join ?..sysobjects o
on o.uid = u.uid"
where
u.name like '%moh60108'"
'?' represents the current database.
-
It's giving you that error because you are using double quotes. Use single quotes and then within the query text, use two single quotes to denote a single single quote:
EXEC
sp_Msforeachdb @command1 = -- Looks through all databases on server
'Select o.name,u.name,o.uid,d.name
from ?..sysusers u,?..sysobjects o, ?..sysdatabases d
where (u.name like ''%moh60108'' and o.uid = u.uid)'
That, of course, will give you a bunch of errors because sysdatabases is only in master, not in all databases. Use SuperChick's advice and simply add '?' to the select:
EXEC sp_Msforeachdb @command1 = -- Looks through all databases on server
'Select o.name,u.name,o.uid, ''?''
from ?..sysusers u,?..sysobjects o
where (u.name like ''%moh60108'' and o.uid = u.uid)'
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply