签到成功

知道了

CNDBA社区CNDBA社区

SQL Server 常见的系统存储过程

2023-02-25 07:37 583 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)约束、视图、系统对象(系统存储过程)的代码文本定义。

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

示例:

--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(数据库详情)等。

示例:

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

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

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

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

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 当前数据库是系统数据库

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

示例:

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

功能及说明:对某个数据库重命名http://www.cndba.cn/dave/article/116454

示例:

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。

示例

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

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

示例:

 -- 启用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

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

参数形式为:

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

示例:

-- 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' ]
 [;]

示例:

-- 创建一个登录名并制定密码和默认所属数据库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

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

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

-- 删除登录名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用户)。

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

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

功能及说明:查看所有数据库的信息(名称、数据库大小)。

示例:

EXEC sp_databases

20 sp_column_privileges

功能及说明:查看当前数据库里查看指定表的字段权限(INSERTREFERENCES、SELECT、UPDATE)。
参数形式

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

关注

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

  • 2235
    原创
  • 2
    翻译
  • 534
    转载
  • 185
    评论
  • 访问:6324398次
  • 积分:4222
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ