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

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 uINNER JOIN sys.server_role_members m ON u.principal_id = m.member_principal_idINNER 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 uINNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_idINNER 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.typeWHEN '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.PROTECTTYPEWHEN 204 THEN 'GRANT_W_GRANT'WHEN 205 THEN 'GRANT'WHEN 206 THEN 'DENY'ELSE 'OTHER'END AS ProtectTypefrom sysprotects a inner join sysobjects b on a.id = b.idinner 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_namefrom sys.databases d

inner join sys.master_files mdf on

d.database_id = mdf.database_id and mdf.[type] = 0inner join sys.master_files ldf on

d.database_id = ldf.database_id and ldf.[type] = 1go-------------------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 

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_MBfrom #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