SqlServer查看数据库信息及服务器级、数据库级、数据库独立 用户权限

--数据库清单
SELECT * FROM Master..SysDatabases ORDER BY Name;  

--服务器级用户权限
WITH CTE AS
(
SELECT u.name AS UserName,
u.is_disabled AS IsDisabled,
g.name as svrRole,
'√' as 'flag'
FROM sys.server_principals u
INNER JOIN sys.server_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.server_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR svrRole IN ([public],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin])) as rg;

--数据库级用户权限
WITH CTE AS
(
SELECT u.name AS UserName,
g.name AS dbRole,
'√' as 'flag'
FROM sys.database_principals u
INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id
INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id
)
SELECT * FROM CTE PIVOT(MAX(flag) FOR dbRole IN ([public],
[db_owner],
[db_accessadmin],
[db_securityadmin],
[db_ddladmin],
[db_backupoperator],
[db_datareader],
[db_datawriter],
[db_denydatareader],
[db_denydatawriter])) as rg;

--数据库级独立用户权限
select c.name as UserName,b.name as ObjectName,
CASE b.type
WHEN 'U' THEN 'Table'
WHEN 'P' THEN 'Procedure'
ELSE 'OTHER'
END AS ObjectType,
CASE WHEN a.ACTION = 26 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'REFERENCES',
CASE WHEN a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'SELECT',
CASE WHEN a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'INSERT',
CASE WHEN a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'UPDATE',
CASE WHEN a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'DELETE',
CASE WHEN a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN '√' ELSE '' END AS 'EXECUTE',
CASE a.PROTECTTYPE
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
ELSE 'OTHER'
END AS ProtectType
from sysprotects a inner join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid order by c.name,b.name

 

获取更全面的数据库信息：

------------------------------data file size----------------------------

if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname varchar(150),dbstatus varchar(150),Recovery_Model varchar(150) default ('NA'), Collation_Name varchar(150),File_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),Free_Space_MB decimal(20,2) default (0))
go 
 
insert into #dbsize(Dbname,dbstatus,Recovery_Model,Collation_Name,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
    CONVERT(varchar(150),DatabasePropertyEx(''?'',''Status'')) , 
    CONVERT(varchar(150),DatabasePropertyEx(''?'',''Recovery'')), 
    CONVERT(varchar(150),DatabasePropertyEx(''?'',''Collation'')), 
    sum(size)/128.0 AS File_Size_MB,
    sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
    SUM(size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB 
  from sys.database_files  where type=0 group by type'
 
go

-------------mdf、ldf----------------
if exists (select * from tempdb.sys.all_objects where name like '#dbFiles%')
drop table #dbFiles
create table #dbFiles
(Dbname varchar(150), Data_File varchar(max), Log_File varchar(max))
go
 
insert into #dbFiles(Dbname,Data_File,Log_File)
select 
    d.name,
    mdf.physical_name,
    ldf.physical_name
from sys.databases d
inner join sys.master_files mdf on 
    d.database_id = mdf.database_id and mdf.[type] = 0
inner join sys.master_files ldf on 
    d.database_id = ldf.database_id and ldf.[type] = 1

go

-------------------Log size-------------------
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname varchar(150), Log_File_Size_MB decimal(20,2)default (0),log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0))
go
 
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB 
from sys.database_files  where type=1 group by type'
  
go

----------------------------database free size--------------------
  if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name varchar(150),
database_size varchar(150),
Freespace varchar(150) default (0.00))
 
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use ?;SELECT database_name = db_name()
    ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2))
    ,''unallocated space'' = ltrim(str((
                CASE 
                    WHEN dbsize >= reservedpages
                        THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
                    ELSE 0
                    END
                ), 15, 2))
FROM (
    SELECT dbsize = sum(convert(BIGINT, CASE 
                    WHEN type = 0
                        THEN size
                    ELSE 0
                    END))
        ,logsize = sum(convert(BIGINT, CASE 
                    WHEN type <> 0
                        THEN size
                    ELSE 0
                    END))
    FROM sys.database_files
) AS files
,(
    SELECT reservedpages = sum(a.total_pages)
        ,usedpages = sum(a.used_pages)
        ,pages = sum(CASE 
                WHEN it.internal_type IN (
                        202
                        ,204
                        ,211
                        ,212
                        ,213
                        ,214
                        ,215
                        ,216
                        )
                    THEN 0
                WHEN a.type <> 1
                    THEN a.used_pages
                WHEN p.index_id < 2
                    THEN a.data_pages
                ELSE 0
                END)
    FROM sys.partitions p
    INNER JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    LEFT JOIN sys.internal_tables it
        ON p.object_id = it.object_id
) AS partitions'
-----------------------------------
 
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate 
create table #alldbstate 
(dbname varchar(150),
DBstatus varchar(150),
R_model Varchar(150))
 
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(150),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize
 
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'
 
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'
 
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'
 
select 
d.Dbname, d.dbstatus, d.Recovery_Model, d.Collation_Name,
(file_size_mb + log_file_size_mb) as DBsize_MB, fs.Freespace as DB_Freespace_MB,
df.Data_File,
d.File_Size_MB, d.Space_Used_MB, d.Free_Space_MB,
df.Log_File,
lg.Log_File_Size_MB, Log_Space_Used_MB, lg.Log_Free_Space_MB
from #dbsize d 
join #logsize lg 
on d.Dbname=lg.Dbname 
join #dbfreesize fs 
on d.Dbname=fs.name
join #dbFiles df
on d.Dbname = df.Dbname
order by Dbname