當前位置：首頁 > 运维知识 > windows >内容正文

windows

SQL Server里那些我们应该知道的系统存储过程

發布時間：2024/9/27 windows 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 SQL Server里那些我们应该知道的系统存储过程小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

SQL Server常見系統存儲過程

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

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

-- 2其它help類系統存儲過程

USE?AdventureWorks2019

--查看指定表的觸發器信息

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

EXEC?sp_depends?@objname?=?N'Production.iWorkOrder'?;?

5 sp_detach_db sp_attach_db

功能及說明:

sp_detach_db是對數據庫進行分離（卸載），以下幾種情況不能分離：

1 數據庫正在用時

2 數據庫處理復制狀態

3 數據庫的快照已存在

4數據庫正在被鏡像

5 數據庫已經被掛起

6 當前數據庫是系統數據庫

示例：

USE master;?

ALTER DATABASE AdventureWorks2012?

SET SINGLE_USER;?

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';?

--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';?

13 sp_password

功能及說明：修改SQL Server登錄名的密碼。

--1修改用戶John的密碼為新密碼，這里無需知道舊密碼

ALTER LOGIN John WITH PASSWORD = 'G368630s#2_36';?

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

-- 刪除用戶名Albert

EXEC sp_dropuser 'Albert';

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

功能及說明：查看所有數據庫的信息（名稱、數據庫大小）。

示例：

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

示例：

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

總結

以上是生活随笔為你收集整理的SQL Server里那些我们应该知道的系统存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： 10派7元是什么意思
下一篇： Linux 常见命令之Find \； +