Hi Deepak,

you did a good job and posted a clear question. However, it would make things a lot easier for us if you also included the CREATE TABLE statement for...

You could also post what you have done so far and we could try to...

Could be something similar to this:

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT ''?'' AS DBNAME, COUNT(*) AS CNT FROM TableName WHERE SomeField = 1'

DECLARE @results TABLE (

DBNAME sysname,

CNT int

)

INSERT @results

EXEC sp_MSForEachDB...

I think I will have to wait 40 years more for that, at best. They keep raising the retirement minimum...

I see that problems are the same all around the world.... 😀

In Italy there's no such thing as Sr. DBA.

The few places that...

Stefan Krzywicki (3/31/2011)

---

Gianluca Sartori (3/31/2011)

---

Stefan Krzywicki (3/31/2011)

---

Gianluca Sartori (3/31/2011)

---

Wow, Roy! It takes me three years to earn that money!

I would go, if I wasn't certain that my wife would kill...

Stefan Krzywicki (3/31/2011)

---

What does your wife...

Wow, Roy! It takes me three years to earn that money!

I would go, if I wasn't certain that my wife would kill me.

This query will return all your sysadmin users:

SELECT SP.name, type_desc

FROM master.sys.server_principals AS SP

INNER JOIN master.sys.server_role_members SRM

ON SP.principal_id = SRM.member_principal_id

WHERE role_principal_id = (

SELECT principal_id

FROM master.sys.server_principals

WHERE name = 'sysadmin'

)

AND is_disabled = 0

I'm afraid it won't work. At least it would be useless, because a sysadmin could always re-enable access for his account.

From your private message:

The first entry for the google search I suggested points here:

http://msdn.microsoft.com/en-us/library/ms175475.aspx

Does this...

Exactly.

I don't think so. By default, members of the local administrators group are granted the sysadmin role, but it's quite a different thing.

You could check index access statistics for the tables in the database:

DECLARE @accessStats TABLE (

db_name sysname,

last_access datetime

)

INSERT @accessStats

EXEC sp_msForEachDb '

SELECT ''?'', MAX(last_access) AS last_access

FROM (

SELECT DB_NAME() AS db_name,

MAX(last_user_seek) ...