SQL_Sever数据库常用语句大全.zip

SQL Server 中4个系统数据库，Master、Model、Msdb、Tempdb。 （1）Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其他系统数据库及用户数据库的相关信息。因此，如果 master 数据库不可用，则 SQL Server 无法启动。在 SQL Server 2005 中，系统对象不再存储在 master 数据库中，而是存储在 Resource 数据库中。 （2）model 数据库用作在 SQL Server 实例上创建的所有数据库的模板。因为每次启动 SQL Server 时都会创建 tempdb，所以 model 数据库必须始终存在于 SQL Server 系统中。当发出 CREATE DATABASE（创建数据库）语句时，将通过复制 model 数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。 如果修改 model 数据库，之后创建的所有数据库都将继承这些修改。例如，可以设置权限或数据库选项或者添加对象，例如，表、函数或存储过程。 （3）Msdb数据库是代理服务数据库，为其报警、任务调度和记录操作员的操作提供存储空间。 （4）Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其他临时操作提供存储空间。Tempdb数据库由整个系统的所有数据库使用，不管用户使用哪个数据库，他们所建立的所有临时表和存储过程都存储在tempdb上。SQL Server每次启动时，tempdb数据库被重新建立。当用户与SQL Server断开连接时，其临时表和存储过程自动被删除。 SQL Server 中4个系统数据库，Master、Model、Msdb、Tempdb。 （1）Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其他系统数据库及用户数据库的相关信息。因此，如果 master 数据库不可用，则 SQL Server 无法启动。在 SQL Server 2005 中，系统对象不再存储在 master 数据库中，而是存储在 Resource 数据库中。 （2）model 数据库用作在 SQL Server 实例上创建的所有数据库的模板。因为每次启动 SQL Server 时都会创建 tempdb，所以 model 数据库必须始终存在于 SQL Server 系统中。当发出 CREATE DATABASE（创建数据库）语句时，将通过复制 model 数据库中的内容来创建数据库的第一部分，然后用空页填充新数据库的剩余部分。 如果修改 model 数据库，之后创建的所有数据库都将继承这些修改。例如，可以设置权限或数据库选项或者添加对象，例如，表、函数或存储过程。 （3）Msdb数据库是代理服务数据库，为其报警、任务调度和记录操作员的操作提供存储空间。 （4）Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其他临时操作提供存储空间。Tempdb数据库由整个系统的所有数据库使用，不管用户使用哪个数据库，他们所建立的所有临时表和存储过程都存储在tempdb上。SQL Server每次启动时，tempdb数据库被重新建立。当用户与SQL Server断开连接时，其临时表和存储过程自动被删除。 1. sqlserver查看实例级别的信息，使用SERVERPROPERTY函数 select SERVERPROPERTY ('propertyname') 2. 查看实例级别的某个参数XX的配置 select * from sys.configurations where name='XX' 3. 更改实例级别的某个参数XX的值 sp_configure 'XX','0' RECONFIGURE WITH OVERRIDE sp_configure显示或更改当前服务器的全局配置设置。 RECONFIGURE表示SQL Server不用重新启动就立即生效 。 使用sp_configure更改设置时，请使用RECONFIGURE语句使更改立即生效，否则更改将在SQL Server重新启动后生效。RECONFIGURE后面加WITH OVERRIDE表示不管这个值是不是符合要求都会生效，比如recovery interval的范围值是10--60对应sys.configurations.minimum是10、sys.configurations.maximum是60，如果sp_configure 'recovery interval', 75设置为75，超过了这个10--60规范，但是要让75生效，则必须加上WITH OVERRIDE。 4. sqlserver没有系统表可以查询所有数据库下面对象，以下只能在当前数据库下面查 select * from sys.all_objects --查询当前数据库的所有架构范围的对象 select * from sys.sysobjects --查询当前数据库的所有对象 --sys.all_objects、sys.sysobjects 这种的视图，在每个数据库的系统视图下面都有 select * from sys.databases --在当前数据库下可以查询到所有数据库信息，包含是否on状态 select * from sys.sysdatabases --在当前数据库下可以查询到所有数据库信息，不包含是否on状态，这个系统视图会在后续的版本中删除 --sys.databases、sys.sysdatabases这种的视图，在每个数据库的系统视图下面都有 sys.processes --没有这个视图 select * from sys.sysprocesses --在当前数据库下可以查询所有正在SQL Server 实例上运行的进程的相关信息，也就是所有数据库上的线程，这个系统视图会在后续的版本中删除 5. 全局系统视图、单个数据库系统视图 sys.database_files --每个存储在数据库本身中的数据库文件在表中占用一行。这是一个基于每个数据库的视图。 sys.master_files --master 数据库中的每个文件对应一行。这是一个系统范围视图。 --sys.database_files、sys.master_files这种的视图，在每个数据库的系统视图下面都有 6. 一些只存在msdb的系统表，而非系统视图 dbo.backupset dbo.log_shipping_secondary dbo.restorehistory dbo.sysjobs dbo.sysjobhistory --这些系统表只存在msdb数据库，使用的时候必须加上msdb前缀 7. sp_lock、sp_who、sp_who2、sp_helptext等一些系统存储过程存在于每个数据库中 8. 报告有关锁的信息，会显示实例里面的所有数据库的锁信息、堵塞信息 sp_lock 9. 提供有关当前用户、 会话和进程的实例中的信息，可以看到会话的状态running、SUSPENDED、sleeping、rollback，sp_who2通过CPUTime、DiskIO可以判断对应的transaction是否很大 sp_who sp_who2 sp_who2 active (可选参数LoginName, 或active代表活动会话数) CPUTime (进程占用的总CPU时间) DiskIO (进程对磁盘读的总次数) LastBatch (客户最后一次调用存储过程或者执行查询的时间) ProgramName (用来初始化连接的应用程序名称，或者主机名) 10. 查看某个存储过程的内容 sp_helptext pro_name 11.显示某个线程号发送到sqlserver数据库的最后一个语句 DBCC INPUTBUFFER 12.假设查询到249被锁给堵塞了，查询被堵塞的SQL语句 DBCC INPUTBUFFER (249) 13. 查看某个数据库中是否存在活动事务，有活动事务就一定会写日志 DBCC OPENTRAN (dbname) 14. 监视日志空间 DBCC SQLPERF (LOGSPACE) 15. 查找无法重用日志中的空间的原因(日志无法截断导致日志文件越来越大，但是可用空间很小，无法收缩) select name,log_reuse_wait_desc from sys.databases 16. 查看虚拟日志文件信息 DBCC LOGINFO 结果有多少行，代表有多少虚拟日志文件，活动的虚拟日志文件的状态(status)为2 17. 修复msdb数据库，比如ssms页面sql server agent丢失或看不了job view history等功能，说明msdb坏了，需要修复 dbcc checkdb (msdb); 18. 在您当前连接到的 SQL Server 数据库中生成一个手动检查点 CHECKPOINT [ checkpoint_duration ] --checkpoint_duration表示以秒为单位指定手动检查点完成所需的时间，一般不使用这个参数，让数据库自己控制 19. 查看数据库各种设置 select name,State,user_access,is_read_only,recovery_model from sys.databases 20. 查看某个数据库中是否存在会话 select DB_NAME(dbid),* from sys.sysprocesses where dbid=db_id('dbname') 21. 查询当前阻塞的所有请求 select * from sys.sysprocesses where blocked>0 或 SELECT t1.resource_type,db_name(t1.resource_database_id),t1.resource_associated_entity_id,t1.request_mode, t1.request_session_id,t2.blocking_session_id,t2.wait_duration_ms FROM sys.dm_tran_locks as t1 INNER JOIN sys.dm_os_waiting_tasks as t2 ON t1.lock_owner_address = t2.resource_address; 或 select A.SPID as 被阻塞进程,a.CMD AS 正在执行的操作,b.spid AS 阻塞进程号,b.cmd AS 阻塞进程正在执行的操作 from master..sysprocesses a,master..sysprocesses b where a.blocked<>0 and a.blocked= b.spid 或 SELECT session_Id,spid,ecid,DB_NAME (sp.dbid),nt_username,er.status,wait_type, [Individual Query] =SUBSTRING (qt.text,er.statement_start_offset / 2, ( CASE WHEN er.statement_end_offset = -1 THEN LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2), qt.text,program_name,Hostname,nt_domain,start_time FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text (er.sql_handle) AS qt WHERE session_Id > 50 /* Ignore system spids.*/ AND sp.blocked>0 AND session_Id NOT IN (@@SPID) 或 SELECT session_id ,status ,blocking_session_id ,wait_type ,wait_time ,wait_resource ,transaction_id FROM sys.dm_exec_requests WHERE status = N'suspended'; --sys.dm_exec_requests返回SQL Server 中正在执行的每个请求的信息 22. 查看哪些表被锁了，以及这些表被哪个进程锁了 select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT' ORDER BY request_session_id ASC 23. 查询某个job是否被堵塞 select * from msdb.dbo.sysjobs where name='jobname' select a.program_name,a.* from master.