Sql query to find all database with users in a dbserver

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName 

SQL Query to find database size of all databases in server

In some situations we might need to find the sizes of all databases in the server. In case we dont have masterfile access we can use the following query:


DECLARE @DBsize_sql VARCHAR(4000)
DECLARE @DBsize_table TABLE (DBName VARCHAR(200), DbFileName VARCHAR(250), CurrentSizeMB numeric(18,4), FreeSpaceMB numeric(18,4))
SET @DBsize_sql='Use ? SELECT DB_NAME() AS DbName, Name AS DbFileName, size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files'
INSERT @DBsize_table
EXEC sp_MSforeachdb @command1=@DBsize_sql
SELECT * FROM @DBsize_table
ORDER BY DbFileName