获取SqlServer数据库、表（字段、索引..）、视图、存储过程等信息脚本
一个服务器（实例）下：
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] & 1 = 1 THEN 'Sunday' ELSE '' END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' WHEN 32 THEN 'Occurs on ' + CASE [freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + ' ' + CASE [freq_interval] WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'Day' WHEN 9 THEN 'Weekday' WHEN 10 THEN 'Weekend day' END + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' else 'unknown' END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') else 'unkown' END [Frequency] ,job.category_id ,job.[description] as job_description ,his.sql_message_id ,his.sql_severity ,his.[message] as job_message ,his.run_status as last_run_status ,job.date_created ,'LastRunDateTime' = CASE WHEN his.run_date = 0 THEN null ELSE msdb.dbo.agent_datetime(his.run_date, his.run_time) END ,'NextRunDateTime' = CASE WHEN sch.next_run_date = 0 THEN null ELSE msdb.dbo.agent_datetime(sch.next_run_date, sch.next_run_time) END ,dateadd(second,(run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100),msdb.dbo.agent_datetime(his.run_date, his.run_time)) as LastRunFinishDateTime ,((run_duration / 10000 * 3600 + (run_duration / 100) % 100 * 60 + run_duration % 100)) AS 'RunDurationSeconds' FROM msdb..sysjobschedules AS sch INNER JOIN msdb..sysjobs AS job ON sch.job_id = job.job_id inner join msdb..sysschedules as schs on sch.schedule_id=schs.schedule_id LEFT JOIN (select hi.* from msdb..sysjobhistory as hi inner join (select job_id,max(instance_id) as instance_id from msdb..sysjobhistory jh where jh.step_id = 0 GROUP BY jh.job_id) as jh on hi.job_id = jh.job_id and hi.instance_id = jh.instance_id) AS his ON his.job_id = job.job_id ) select isnull(job_id, null) as job_id, isnull(InstanceName,'')as InstanceName, isnull(job_name,'')as job_name, isnull(job_enabled,0) as job_enabled, isnull(ScheduleName,'')as ScheduleName, isnull(IsEnabled,'')as IsEnabled, isnull(ScheduleType, '')as ScheduleType, isnull(Occurrence, '')as Occurrence, isnull(Recurrence,'')as Recurrence, isnull(Frequency,'')as Frequency, isnull(category_id, 0)as category_id, isnull(job_description , '')as job_description, isnull(sql_message_id, 0)as sql_message_id, isnull(sql_severity,0)as sql_severity, isnull(job_message , '')as job_message, isnull(last_run_status,0)as last_run_status, isnull(date_created , '1900-01-01')as date_created, isnull(LastRunDateTime,'1900-01-01')as LastRunDateTime, isnull(NextRunDateTime,'1900-01-01')as NextRunDateTime, isnull(LastRunFinishDateTime,'1900-01-01')as LastRunFinishDateTime, isnull(RunDurationSeconds,0)as RunDurationSeconds from testtemp where job_name<>'syspolicy_purge_history'
5、实例版本及配置信息
SELECT @@version as [version] SELECT * FROM sys.configurations c
6、获取所有数据库缺失的索引
use [master] SELECT avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS PossibleImprovement ,equality_columns ,included_columns ,last_user_seek ,last_user_scan ,statement AS [Object] ,'CREATE INDEX [IX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' + CONVERT(VARCHAR,D.Index_Handle) + '_' + REPLACE(REPLACE(REPLACE([statement],']',''),'[',''),'.','') + ']' +' ON ' + [statement] + ' (' + ISNULL (equality_columns,'') + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + included_columns + ')', '') AS Create_Index_Syntax FROM sys.dm_db_missing_index_groups AS G INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON GS.group_handle = G.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle Order By PossibleImprovement DESC
7、获取高频大量被更新，但低频使用的索引
SELECT DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , i.type_desc , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] INTO #TempUnusedIndexes FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = o.object_id WHERE 1=2 EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , i.type_desc , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = o.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC' SELECT * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes
8、获取默认Backup、DATA路径
--仅测试：2008 R2、2012 可用 -- 获取Backup路径 EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory' -- 获取DATA路径，方式1 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id = 1 AND file_id = 1 -- 获取DATA路径，方式2 use master --或指定的数据名 declare @path varchar(8000) select @path= rtrim(reverse(filename)) from sysfiles set @path=reverse(substring(@path,charindex('\',@path),8000)) select @path
9、获取所有数据库连接会话
SELECT * FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES] )
一个数据库下：
1、所有架构：
--仅用户创建的 select s.name as schema_name, s.schema_id, u.name as schema_owner from sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id where u.issqluser = 1 and u.name not in ('sys', 'guest', 'INFORMATION_SCHEMA') --所有 select s.name as schema_name, s.schema_id, u.name as schema_owner from sys.schemas s inner join sys.sysusers u on u.uid = s.principal_id order by s.name
2、所有用户：
select name as username, create_date, modify_date, type_desc as type, authentication_type_desc as authentication_type, default_schema_name from sys.database_principals where type not in ('A', 'G', 'R', 'X') and sid is not null and name != 'guest' order by username;
3、所有表：
select schema_name(t.schema_id) as schema_name, t.name as table_name, OBJECTPROPERTY(OBJECT_ID(''+schema_name(t.schema_id)+'.'+t.name+''),'TableHasIdentity') as has_identity, --是否有标识列属性 t.create_date, t.modify_date, f.value as [description] from sys.tables t left join sys.extended_properties f on t.object_id = f.major_id and f.minor_id = 0 order by schema_name, table_name;
4、所有表字段（2021-10-09，修正同表名、架构不同时结果重复的错误；2021-11-1；2021-11-24 获取设定的字段长度）：
select schema_name(tab.schema_id) as [schema_name], tab.name as table_name, col.column_id, col.name as column_name, t.name as data_type, (case cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(6)) when '-1' then 'max' else cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(6)) end) [data_length], col.max_length, col.precision, case when exists ( SELECT 1 FROM sysobjects where xtype='PK' and name in ( SELECT name FROM sysindexes WHERE indid in ( SELECT indid FROM sysindexkeys WHERE id=col.object_id AND colid= col.column_id ) ) ) then 1 else 0 end is_identity, ep.value AS [description], c.COLLATION_NAME [collation], c.COLUMN_DEFAULT default_value, col.is_nullable from sys.tables as tab inner join sys.columns as col on tab.object_id = col.object_id left join sys.types as t on col.user_type_id = t.user_type_id --2021-11-24 c.ORDINAL_POSITION = col.column_id 是错误的，当前表中字段有后来被删除的，c.ORDINAL_POSITION 是 1~n 的数字连续排序，但 col.column_id（列id唯一）不是连续数字 --left join INFORMATION_SCHEMA.COLUMNS c on c.ORDINAL_POSITION = col.column_id and c.TABLE_NAME = tab.name and c.TABLE_SCHEMA = schema_name(tab.schema_id) left join INFORMATION_SCHEMA.COLUMNS c on c.COLUMN_NAME = col.name and c.TABLE_NAME = tab.name and c.TABLE_SCHEMA = schema_name(tab.schema_id) left join sys.extended_properties ep on ep.major_id = col.object_id and ep.minor_id = col.column_id --left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.TABLE_NAME = tab.name and tc.TABLE_SCHEMA = schema_name(tab.schema_id) --此脚本中未用到此句，仅屏蔽，作为笔记 --left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc on cc.CONSTRAINT_NAME=tc.CONSTRAINT_NAME and cc.TABLE_SCHEMA = schema_name(tab.schema_id) --此脚本中未用到此句，仅屏蔽，作为笔记 --where schema_name(tab.schema_id)+'.'+tab.name = 'dbo.Table1' --可根据实际情况使用where语句 order by schema_name, table_name, column_id;
5、所有表、视图索引：
select i.[name] as index_name, substring(column_names, 1, len(column_names)-1) as [columns], case when i.[type] = 1 then 'Clustered index' when i.[type] = 2 then 'Nonclustered index' when i.[type] = 3 then 'XML index' when i.[type] = 4 then 'Spatial index' when i.[type] = 5 then 'Clustered columnstore index' when i.[type] = 6 then 'Nonclustered columnstore index' when i.[type] = 7 then 'Nonclustered hash index' end as index_type, case when i.is_unique = 1 then 'Unique' else 'Not unique' end as [unique], schema_name(t.schema_id) + '.' + t.[name] as table_view, case when t.[type] = 'U' then 'Table' when t.[type] = 'V' then 'View' end as [object_type] from sys.objects t inner join sys.indexes i on t.object_id = i.object_id cross apply (select col.[name] + ', ' from sys.index_columns ic inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id where ic.object_id = t.object_id and ic.index_id = i.index_id order by key_ordinal for xml path ('') ) D (column_names) where t.is_ms_shipped <> 1 and index_id > 0 order by i.[name]
6、所有表触发器：
select trg.name as trigger_name, schema_name(tab.schema_id) + '.' + tab.name as [table], case when is_instead_of_trigger = 1 then 'Instead of' else 'After' end as [activation], (case when objectproperty(trg.object_id, 'ExecIsUpdateTrigger') = 1 then 'Update ' else '' end + case when objectproperty(trg.object_id, 'ExecIsDeleteTrigger') = 1 then 'Delete ' else '' end + case when objectproperty(trg.object_id, 'ExecIsInsertTrigger') = 1 then 'Insert' else '' end ) as [event], case when trg.parent_class = 1 then 'Table trigger' when trg.parent_class = 0 then 'Database trigger' end [class], case when trg.[type] = 'TA' then 'Assembly (CLR) trigger' when trg.[type] = 'TR' then 'SQL trigger' else '' end as [type], case when is_disabled = 1 then 'Disabled' else 'Active' end as [status], object_definition(trg.object_id) as [definition] from sys.triggers trg left join sys.objects tab on trg.parent_id = tab.object_id order by trg.name;
7、所有视图：
SELECT sch.name schema_name, o.name, m.definition FROM sys.sql_modules AS m inner join sys.all_objects AS o ON m.object_id = o.object_id inner join sys.schemas as sch on sch.schema_id = o.schema_id WHERE o.[type] = 'v'
8、所有视图及其列：
select schema_name(v.schema_id) as schema_name, object_name(c.object_id) as view_name, c.column_id, c.name as column_name, type_name(user_type_id) as data_type, c.max_length, c.precision from sys.columns c join sys.views v on v.object_id = c.object_id order by schema_name, view_name, column_id;
9、所有存储过程：
SELECT sch.name schema_name, o.name proc_name, m.definition FROM sys.sql_modules AS m INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id inner join sys.schemas as sch on sch.schema_id = o.schema_id WHERE o.[type] = 'p'
10、所有存储过程及其参数：
SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], SO.name AS [ObjectName], SO.Type_Desc AS [ObjectType (UDF/SP)], P.parameter_id AS [ParameterID], P.name AS [ParameterName], TYPE_NAME(P.user_type_id) AS [ParameterDataType], P.max_length AS [ParameterMaxBytes], P.is_output AS [IsOutPutParameter] FROM sys.objects AS SO INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID WHERE so.type = 'P' ORDER BY [Schema], SO.name, P.parameter_id GO
11、所有函数：
SELECT sch.name schema_name, o.name func_name, m.definition FROM sys.sql_modules AS m INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id inner join sys.schemas as sch on sch.schema_id = o.schema_id WHERE o.[type] in ('FN', 'IF', 'TF')
12、所有函数及其参数：
SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], SO.name AS [ObjectName], SO.Type_Desc AS [ObjectType (UDF/SP)], P.parameter_id AS [ParameterID], P.name AS [ParameterName], TYPE_NAME(P.user_type_id) AS [ParameterDataType], P.max_length AS [ParameterMaxBytes], P.is_output AS [IsOutPutParameter] FROM sys.objects AS SO INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID WHERE so.type in ('FN', 'IF', 'TF') ORDER BY [Schema], SO.name, P.parameter_id GO
13、当前数据库所有文件
SELECT dbf.file_id AS FileID , dbf.name AS [LogicName] , s.filename AS PhysFileFullName , SUBSTRING(s.filename,1,len(s.filename)- CHARINDEX('\',REVERSE(s.filename)))+'\' as [PhysPath] , replace(s.filename,SUBSTRING(s.filename,1,len(s.filename)- CHARINDEX('\',REVERSE(s.filename)))+'\','') as [PhysFileName] , CAST(dbf.size/128.0 AS DECIMAL(19,2)) AS FileSizeMB , CAST(CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(19,2)) AS SpaceUsedMB , CAST(dbf.size/128.0 - CAST(FILEPROPERTY(dbf.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(19,2)) AS AvailableSpaceMB , CAST((dbf.size / 128.0 - (FILEPROPERTY(dbf.name, 'SpaceUsed') / 128.0)) / (dbf.size / 128.0) * 100 AS DECIMAL(19,2)) AS [%FreeSpace] , dbf.growth / 128 AS FileGrowthMB , f.name AS FilegroupName FROM sys.database_files dbf INNER JOIN sys.sysfiles s ON dbf.name = s.name LEFT JOIN sys.filegroups f ON dbf.data_space_id = f.data_space_id ORDER BY dbf.name;
14、表占用空间大小信息
select schema_name(tab.schema_id) + '.' + tab.name as [table], cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as UsedSpace_MB, --占用磁盘空间大小 cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as Allocated_MB --分配磁盘空间大小 from sys.tables tab inner join sys.indexes ind on tab.object_id = ind.object_id inner join sys.partitions part on ind.object_id = part.object_id and ind.index_id = part.index_id inner join sys.allocation_units spc on part.partition_id = spc.container_id where schema_name(tab.schema_id) + '.' + tab.name='{$$TableName}' group by schema_name(tab.schema_id) + '.' + tab.name order by sum(spc.used_pages) desc
15、表分区字段
select c.name partition_column from sys.tables t join sys.indexes i on(i.object_id = t.object_id and i.index_id < 2) join sys.index_columns ic on(ic.partition_ordinal > 0 and ic.index_id = i.index_id and ic.object_id = t.object_id) join sys.columns c on(c.object_id = ic.object_id and c.column_id = ic.column_id) where t.object_id = object_id('dbo.table1')
16、检查数据库是否存在错误（对于巨大数据库，执行时间可能持续若干小时）
DECLARE @tbDbcc Table ( [Error] [int] NULL, [Level] [int] NULL, [State] [int] NULL, [MessageText] [varchar](max) NULL, [RepairLevel] [varchar](200) NULL, [Status] [int] NULL, [DbId] [int] NULL, [DbFragId] [int] NULL, [ObjectId] [int] NULL, [IndexId] [int] NULL, [PartitionID] [bigint] NULL, [AllocUnitID] [bigint] NULL, [RidDbtId] [int] NULL, [RidDPrutId] [int] NULL, [File] [int] NULL, [Page] [int] NULL, [Slot] [int] NULL, [RefDbId] [int] NULL, [RefPruId] [int] NULL, [RefFile] [int] NULL, [RefPage] [int] NULL, [RefSlot] [int] NULL, [Allocation] [int] NULL ) INSERT INTO @tbDbcc ([Error], [Level], [State], MessageText, RepairLevel, [Status], [DbId], [DbFragId], [ObjectId], [IndexId], PartitionId, AllocUnitId, [RidDbtId], [RidDPrutId] ,[File], [Page], Slot,[RefDbId], [RefPruId], RefFile, RefPage, RefSlot,Allocation) EXEC ('DBCC CHECKDB(''指定的数据库名'') WITH TableResults') select * from @tbDbcc where [RepairLevel] is not null
17、获取指定表的Create脚本
DECLARE @object_name SYSNAME , @object_id INT , @SQL NVARCHAR(MAX) SELECT @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']' , @object_id = [object_id] FROM (SELECT [object_id] = OBJECT_ID('dbo.WorkOut', 'U')) o SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF(( SELECT CHAR(13) + ' , [' + c.name + '] ' + CASE WHEN c.is_computed = 1 THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id) ELSE CASE WHEN c.system_type_id != c.user_type_id THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']' ELSE '[' + UPPER(tp.name) + ']' END + CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')' WHEN tp.name IN ('nvarchar', 'nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')' WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')' WHEN tp.name = 'decimal' THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')' ELSE '' END + CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id THEN ' COLLATE ' + c.collation_name ELSE '' END + CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END + CASE WHEN c.default_object_id != 0 THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' + ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id) ELSE '' END + CASE WHEN cc.[object_id] IS NOT NULL THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition] ELSE '' END + CASE WHEN c.is_identity = 1 THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' + CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')' ELSE '' END END FROM sys.columns c WITH(NOLOCK) JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id LEFT JOIN sys.check_constraints cc WITH(NOLOCK) ON c.[object_id] = cc.parent_object_id AND cc.parent_column_id = c.column_id WHERE c.[object_id] = @object_id ORDER BY c.column_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') + ISNULL((SELECT ' , CONSTRAINT [' + i.name + '] PRIMARY KEY ' + CASE WHEN i.index_id = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END +' (' + ( SELECT STUFF(CAST(( SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END FROM sys.index_columns ic WITH(NOLOCK) WHERE i.[object_id] = ic.[object_id] AND i.index_id = ic.index_id FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')' FROM sys.indexes i WITH(NOLOCK) WHERE i.[object_id] = @object_id AND i.is_primary_key = 1), '') + CHAR(13) + ');' PRINT @SQL
18、获取对象的依赖关系（如视图从哪个表中查询的）
select schema_name(o.schema_id) [schema_name], o.name as [object_name], o.type as [object_type], schema_name(ref_o.schema_id) [ref_schema_name], ref_o.name as [ref_object_name], ref_o.type as [ref_object_type] from sys.objects ref_o join sys.sql_expression_dependencies dep on ref_o.object_id = dep.referenced_id join sys.objects o on dep.referencing_id = o.object_id order by [object_name]
