简易获取SQL Server数据库中的数据、表格（包括字段、索引等）以及视图和存储过程的相关信息脚本

最编程 2024-07-19 14:52:25

...

一个服务器（实例）下：

1、所有数据库：

select [name] as database_name, 
    database_id, 
    create_date
from sys.databases
order by name

2、所有登录账号：

--基本服务器登录账号信息
select sp.name as login_account,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

--服务器登录账号及账号拥有的服务器角色
declare @loginUsers table (serverName sysname, loginName sysname,serverRole sysname)
insert @loginUsers
select @@SERVERNAME serverName, sl.name loginName, isnull(DB_Roles.Roles,'public') serverRole
from sys.syslogins sl
left join
(
select 'sysadmin' as Roles
union all 
select 'securityadmin' 
union all 
select 'serveradmin' 
union all 
select 'setupadmin' 
union all 
select 'processadmin' 
union all 
select 'diskadmin' 
union all 
select 'dbcreator' 
union all 
select 'bulkadmin' 
union all 
select 'No serverRole' 
union all
select 'public'
) DB_Roles 
on 
Case When sl.sysadmin=1 and DB_Roles.Roles='sysadmin' then 'sysadmin' 
When sl.sysadmin = 1 and DB_Roles.Roles = 'public' then 'public'
    When sl.securityadmin=1 and DB_Roles.Roles='securityadmin' then 'securityadmin'
    When sl.serveradmin=1 and DB_Roles.Roles='serveradmin' then 'serveradmin' 
    When sl.setupadmin=1 and DB_Roles.Roles='setupadmin' then 'setupadmin' 
    When sl.processadmin=1 and DB_Roles.Roles='processadmin' then 'processadmin' 
    When sl.diskadmin=1 and DB_Roles.Roles='diskadmin' then 'diskadmin' 
    When sl.dbcreator=1 and DB_Roles.Roles='dbcreator' then 'dbcreator'  
    When sl.bulkadmin=1 and DB_Roles.Roles='bulkadmin' then 'bulkadmin' 
    else null end=DB_Roles.Roles    
order by 2

--select * from @loginUsers

select user1.loginName,
STUFF((SELECT ',' + CONVERT(VARCHAR(500),serverRole)
FROM @loginUsers user2
WHERE
user1.loginName=user2.loginName
FOR XML PATH('')
),1,1,'') AS serverRole 
from @loginUsers user1 group by user1.loginName order by user1.loginName

3、数据库对应用户及用户拥有的数据库角色

--完整
DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
INSERT @DB_USers
EXEC sp_MSforeachdb
'use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
SELECT
dbname,username ,logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')
)
,1,1,'') AS Permissions_user
FROM @DB_USers user1
--where dbname ='master' and username = 'public'
GROUP BY dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username

--简单的
declare @DB_USers table (DBName sysname, UserName sysname)
insert @DB_USers
exec sp_MSforeachdb
'use [?]
SELECT ''?'' AS DB_Name, prin.name AS UserName
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
declare @DB_USers2 table (DBName sysname, UserName sysname)
insert @DB_USers2 select dbname,username from @DB_USers group by dbname,username
select dbname,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),username)
FROM @DB_USers2 user2
WHERE
user1.DBName=user2.DBName
FOR XML PATH('')
),1,1,'') AS username
from @DB_USers2 user1 group by dbname order by dbname

4、所有作业与计划

SELECT
    [job].[job_id] AS '作业唯一标识符'
   ,[job].[name] AS '作业名称'
   ,[jstep].[step_uid] AS '步骤唯一标识符'
   ,[jstep].[step_id] AS '步骤编号'
   ,[jstep].[step_name] AS '步骤名称'
   ,CASE [jstep].[subsystem]
      WHEN 'ActiveScripting' THEN 'ActiveX Script'
      WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
      WHEN 'PowerShell' THEN 'PowerShell'
      WHEN 'Distribution' THEN 'Replication Distributor'
      WHEN 'Merge' THEN 'Replication Merge'
      WHEN 'QueueReader' THEN 'Replication Queue Reader'
      WHEN 'Snapshot' THEN 'Replication Snapshot'
      WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
      WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
      WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
      WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
      WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
      ELSE [jstep].[subsystem]
    END AS '作业步骤类型'
   ,CASE
        WHEN [px].[name] IS NULL THEN 'SQL SERVER代理服务账户'
        ELSE [px].[name]
    END AS '步骤运行账户'
   ,[jstep].[database_name] AS '执行数据库名'
   ,[jstep].[command] AS '执行命令'
   ,CASE [jstep].[on_success_action]
      WHEN 1 THEN '退出报表成功的作业'
      WHEN 2 THEN '退出报告失败的作业'
      WHEN 3 THEN '转到下一步'
      WHEN 4
      THEN '转到步骤: '
           + QUOTENAME(CAST([jstep].[on_success_step_id] AS VARCHAR(3))) + ' '
           + [sOSSTP].[step_name]
    END AS '执行成功后操作'
   ,[jstep].[retry_attempts] AS '失败时的重试次数'
   ,[jstep].[retry_interval] AS '重试间的等待时间(分钟)'
   ,CASE [jstep].[on_fail_action]
      WHEN 1 THEN '退出报告成功的作业'
      WHEN 2 THEN '退出报告失败的作业'
      WHEN 3 THEN '转到下一步'
      WHEN 4
      THEN '转到步骤: '
           + QUOTENAME(CAST([jstep].[on_fail_step_id] AS VARCHAR(3))) + ' '
           + [sOFSTP].[step_name]
    END AS '执行失败后操作'
FROM [msdb].[dbo].[sysjobsteps] AS [jstep]
INNER JOIN [msdb].[dbo].[sysjobs] AS [job]
        ON [jstep].[job_id] = [job].[job_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [jstep].[job_id] = [sOSSTP].[job_id]
           AND [jstep].[on_success_step_id] = [sOSSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [jstep].[job_id] = [sOFSTP].[job_id]
           AND [jstep].[on_fail_step_id] = [sOFSTP].[step_id]
LEFT JOIN [msdb].[dbo].[sysproxies] AS [px]--代理账户信息
        ON [jstep].[proxy_id] = [px].[proxy_id]
ORDER BY [job].[name], [jstep].[step_id]

go

with testtemp as(
SELECT
    sch.job_id,--his.[server] as InstanceName
    CONVERT(nvarchar(150),his.[server]) as InstanceName
    ,--job.NAME as job_name
    CONVERT(nvarchar(150),job.NAME) as job_name
    ,job.[enabled] as job_enabled 
    , --schs.[name] AS [ScheduleName]
    CONVERT(nvarchar(150),schs.[name]) as ScheduleName
    , CASE schs.[enabled]
        WHEN 1 THEN '是'
        WHEN 0 THEN '否'
        else '未知'
      END AS [IsEnabled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'SQL Server代理启动时自动启动'
        WHEN [freq_type] = 128 THEN 'CPU空闲时启动'
        WHEN [freq_type] IN (4,8,16,32) THEN '重复执行'
        WHEN [freq_type] = 1 THEN 'One Time'
        else 'unkown'
      END [ScheduleType]
    , CASE [freq_type]
        WHEN 1 THEN '一次'
        WHEN 4 THEN '每天'
        WHEN 8 THEN '每周'
        WHEN 16 THEN '每月'
        WHEN 32 THEN '每月 - 相对频率间隔'
        WHEN 64 THEN 'SQL Server代理启动时自动启动'
        WHEN 128 THEN 'CPU空闲时启动'
        else 'unkown'
      END [Occurrence]
    , CASE [freq_type]
        WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)'
        WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) 
                    + ' week(s) on '
                    + CASE WHEN [freq_interval

上一篇：如何在SQL Server中创建和使用自定义函数

下一篇： SQL Server 的DDL操作审计触发器简明指南