签到成功

知道了

CNDBA社区CNDBA社区

SQL Server 常见的系统存储过程

2023-02-25 07:37 817 0 转载 SQLServer
作者： dave

1 sp_who

功能及说明:
当前数据库实例的用户、会话、进程信息。
参数主要包括用户(@loginame='xxxx')、会话状态(仅活动的即ACTIVE)、回话ID三个参数里的一个。如果不加参数则返回该数据库实例下的所有会话信息。

返回参数：主要有会话ID、执行计划上下文ID、状态、登录名、主机名、阻塞进程对应的会话ID、数据库名、命令(TSQL、SQL、数据库内部命令代号)、请求ID。

示例：

-- 1 不传参数
sp_who
--EXEC sp_who 等价于上式

--2仅传登录名参数
--EXEC sp_who @loginame='sa'; 
EXEC sp_who 'sa'; --等价于上式

--3仅传状态参数
EXEC sp_who 'active'; 

--4仅传spid参数
exec sp_who  55

2 sp_helptext

功能及说明:返回用户定义的规则、默认值的定义、未加密的用户自定义存储过程(函数)、触发器、计算列、检查(CHECK)约束、视图、系统对象（系统存储过程）的代码文本定义。

示例:

--1查看存储过程的代码定义
sp_helptext @objname ='usp_useradd_MS'

--2查看函数的代码定义
sp_helptext @objname ='fun_GetPage'

--3 查看计算列的定义
USE AdventureWorks2019;  
GO  

sp_helptext @objname = N'AdventureWorks2019.Sales.SalesOrderHeader', @columnname = TotalDue ;  
GO

-- 4 查看系统存储过程sp_who的代码定义
sp_helptext @objname = N'sp_who';

3 sp_help

功能及说明:显示指定数据库对象里的详细信息(对象类型、创建时间、字段类型、是否自增、文件组等信息)。

其它类似的系统存储过程有：

sp_helptrigger(触发器详情)、
sp_helpindex(索引详情)、
sp_helpserver(服务器详情)、
sp_helpstats(统计信息详情)、
sp_helpsort(字符集排序详情)、
sp_helpfilegroup(文件组详情)、
sp_helplanguage(语言详情)、
sp_helpfile(数据库文件详情)、
sp_helpdb(数据库详情)等。

示例：

http://www.cndba.cn/dave/article/116454 

sp_help 'tb_user'

USE AdventureWorks2019
GO

--查看指定表的触发器信息

EXEC sp_helptrigger 'Person.Person'; 

--查看指定表的索引信息
EXEC sp_helpindex N'Sales.Customer'; 

--查看数据库所在实例的服务器信息 
EXEC sp_helpserver N'localhost'; 

--查看指定表的统计信息
EXEC sp_createstats; 
GO 
EXEC sp_helpstats  
@objname = 'Sales.Customer', 
@results = 'ALL';

--查看当前数据库字符集排序
sp_helpsort; 

--查看数据库文件组信息
sp_helpfilegroup 'PRIMARY'

--查看某国语言的格式信息（日期格式、周首日序号、月格式等）
sp_helplanguage 'Simplified Chinese' ;
sp_helplanguage

--查看数据文件和日志的详细信息。
sp_helpfile; 

--查看表对应的约束信息（主键、外键、检查约束、默认值约束等）
EXEC sp_helpconstraint 'Production.Product'; 

--查看数据库的信息(数据库名、大小、所有者、创建时间、数据库状态、兼容级别)
EXEC sp_helpdb

4 sp_depends

功能及说明:
显示数据库对象的依赖信息，比如视图或存储过程里依赖的表或视图或者反过来。

示例：

sp_depends @objname = N'tb_user'

--查看触发器里依赖的表、视图等数据库对象
USE AdventureWorks2019
GO
EXEC sp_depends @objname = N'Production.iWorkOrder' ;

5 sp_detach_db sp_attach_db

功能及说明:
sp_detach_db是对数据库进行分离（卸载），以下几种情况不能分离：

http://www.cndba.cn/dave/article/116454

1 数据库正在用时
2 数据库处理复制状态
3 数据库的快照已存在
4数据库正在被镜像
5 数据库已经被挂起
6 当前数据库是系统数据库

示例：

USE master; 
ALTER DATABASE AdventureWorks2012 
SET SINGLE_USER; 
GO
EXEC sp_detach_db 'AdventureWorks2019', 'true';

2 与之对应的是通过sp_attach_db命令完成数据的附加（装载）
示例：
EXEC sp_attach_db @dbname = N'AdventureWorks2019',  
    @filename1=N'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/Data/AdventureWorks2019_Data.mdf',  
    @filename2=N'C:/Program Files/Microsoft SQL Server/MSSQL13.MSSQLSERVER/MSSQL/Data/AdventureWorks2019_log.ldf';

6 sp_spaceused

功能及说明： 查看当前数据库、数据库对象的空间使用情况

示例:

sp_spaceused
sp_spaceused 'tb_user'

7 sp_renamedb

功能及说明：对某个数据库重命名

示例：

USE master;  
GO  
CREATE DATABASE Accounting;  
GO 
SELECT * FROM sys.databases WHERE name ='Accounting' 
EXEC sp_renamedb N'Accounting', N'Financial';  
GO  
SELECT * FROM sys.databases WHERE name =N'Financial'

8 sp_rename

功能及说明：重命名某个数据库对象（表、字段、索引、约束、统计信息等）

示例：

--1重命名表名
USE AdventureWorks2019;  
GO 
EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr'; 
GO 

--2重命名字段
USE AdventureWorks2019; 
GO 
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN'; 
GO

--3重命名索引
USE AdventureWorks2019; 
GO 
EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX'; 
GO

9 sp_executesql

功能及说明：执行动态SQL。http://www.cndba.cn/dave/article/116454

示例

SET @IntDeptno = 30;  
SET @SQLString = N'SELECT @max_salOUT = max(sal)   
   FROM employee 
   WHERE deptno = @dno';  
SET @ParmDefinition = N'@dno TINYINT, @max_salOUT decimal(7,2) OUTPUT';  
EXECUTE sp_executesql @SQLString, @ParmDefinition, @dno = @IntDeptno, @max_salOUT=@max_sal OUTPUT;  
SELECT @max_sal;

上述代码翻译过来是:
    SELECT max(sal) FROM employee
    WHERE deptno=30

10 sp_configure

功能及说明：进行系统配置。

示例：http://www.cndba.cn/dave/article/116454

 -- 启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--使用完成后，再关闭Ad Hoc Distributed Queries选项
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

11 sp_adduser

功能及说明：给当前数据库加个用户

参数形式为：http://www.cndba.cn/dave/article/116454

sp_adduser [ @loginame = ] 'login'  
    [ , [ @name_in_db = ] 'user' ]  
[ , [ @grpname = ] 'role' ]

示例:http://www.cndba.cn/dave/article/116454

-- 1 将登录名是Vidur的用户Vidur添加到组Recruiting内
EXEC sp_adduser 'Vidur', 'Vidur', 'Recruiting';  
-- 2 增加一个和登录名一样的用户Arvind
EXEC sp_adduser 'Arvind';

12 sp_addlogin

功能及说明：创建一个可以连入SQL Server数据库的登录名。

参数形式为：

sp_addlogin [ @loginame = ] 'login'
[ , [ @passwd = ] 'password' ]
[ , [ @defdb = ] 'database' ]
[ , [ @deflanguage = ] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt = ] 'encryption_option' ]
 [;]

示例：

http://www.cndba.cn/dave/article/116454 

-- 创建一个登录名并制定密码和默认所属数据库corporate。
EXEC sp_addlogin 'Albert', 'B5432-3M6', 'corporate'; 
GO

13 sp_password

功能及说明：修改SQL Server登录名的密码。

--1修改用户John的密码为新密码，这里无需知道旧密码
ALTER LOGIN John WITH PASSWORD = 'G368630s#2_36'; 
GO

--2修改用户John的密码为新密码，这里需输入旧密码
ALTER LOGIN John WITH PASSWORD = 'G368630s#2_36' OLD_PASSWORD = 'H3q1jhygd#2_23236'; GO

14 sp_droplogin sp_dropuser

功能及说明：删除登录名、用户名。

示例：

-- 删除登录名John
EXEC sp_droplogin 'John';
GO
-- 删除用户名Albert
EXEC sp_dropuser 'Albert';
GO

15 sp_table_privileges

功能及说明：SQL Server查看表的权限。

示例：

EXEC sp_table_privileges 'tb_user';

16 sp_tables

功能及说明：SQL Server查看数据库下的所有用户下的表(含sys用户)。

示例：

--1查看当前数据库下所有用户下的表信息。
EXEC sp_tables 

--2查看当前数据库下dbo模式下以emp开头的表信息。
EXEC sp_tables   
   @table_name = 'emp%',  
   @table_owner = 'dbo'

17 sp_stored_procedures

功能及说明：SQL Server查看当前数据库内所有用户下的存储过程(含sys用户)。

示例：

EXEC sp_stored_procedures

18 sp_columns

功能及说明：查看当前数据库内指定表的字段信息 (含sys用户)。

示例：

EXEC sp_columns 'tb_user'

19 sp_databases

功能及说明：查看所有数据库的信息（名称、数据库大小）。

http://www.cndba.cn/dave/article/116454

示例：

EXEC sp_databases

20 sp_column_privileges

功能及说明：查看当前数据库里查看指定表的字段权限(INSERTREFERENCES、SELECT、UPDATE)。
参数形式http://www.cndba.cn/dave/article/116454

sp_column_privileges [ @table_name = ] 'table_name'  
     [ , [ @table_owner = ] 'table_owner' ]  
     [ , [ @table_qualifier = ] 'table_qualifier' ]  
     [ , [ @column_name = ] 'column' ]

示例：

http://www.cndba.cn/dave/article/116454 

USE AdventureWorks2019;  
GO  
EXEC sp_column_privileges @table_name = 'Employee'   
    ,@table_owner = 'HumanResources'  
    ,@table_qualifier = 'AdventureWorks2019'  
    ,@column_name = 'SalariedFlag';

21 sp_MSforeachdb sp_MSforeachtable

sp_MSforeachd、sp_MSforeachtable是两个未公开的系统存储过程，前者用于遍历所有数据库，后者用于遍历某个数据库下所有表。

示例：

--1遍历当前数据库里每个表执行sp_spaceused
exec sp_MSforeachtable @command1="sp_spaceused '?'" 

-- 2结合临时表查看所有数据库下的模式
CREATE TABLE #schemaTable(
   dbname VARCHAR(100),
   schemaname VARCHAR(100)
 )
 EXEC sp_MSforeachdb  @command1="print '?'",
 @command2="INSERT INTO #schemaTable SELECT '?',name schemas_name FROM sys.schemas"
SELECT * FROM #schemaTable
用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2246
    原创
  • 3
    翻译
  • 554
    转载
  • 188
    评论
  • 访问：6919744次
  • 积分：4275
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ