SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' ORDER BY DP1.name;
SELECT name ,
recovery_model_desc ,
state_desc ,
d AS 'Last Full Backup' ,
i AS 'Last Differential Backup' ,
l AS 'Last log Backup'
FROM ( SELECT db.name ,
db.state_desc ,
db.recovery_model_desc ,
type ,
backup_finish_date
FROM master.sys.databases db
LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
) AS Sourcetable
PIVOT
( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup
SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files ORDER BY SizeMB DESC
http://www.bobpusateri.com/archive/2010/09/a-look-at-the-sql-server-browser-service/
SELECT DB_NAME(database_id) as [DB]
, login_name
, nt_domain
, nt_user_name
, status
, host_name
, program_name
, COUNT(*) AS [Connections]
FROM sys.dm_exec_sessions
WHERE database_id > 0 -- OR 4 for user DBs
GROUP BY database_id, login_name, status, host_name, program_name, nt_domain, nt_user_name;
SELECT column, COUNT(column) AS NumOccurrences FROM table GROUP BY column HAVING ( COUNT(column) > 1 )