了解SQL Server中一些常用的查询语句(40个最常用命令)

要教会阅读：1019 2025-02-05 14:15:16 评论：0

前言

SQL是一种标准化的结构化查询语言，而SQL Server是使用较为流行的关系数据库之一。了解 SQL 对软件开发人员也非常重要。在 SQL Server 中，所有数据都使用 SQL 查询进行访问和更新。本文将讨论一些常用的 SQL 查询，了解这些查询可以解决一些复杂的任务。

常用查询

1、查询获取所有数据库的列表

EXEC sp_helpdb

2、查询获取数据库指定存储过程、触发器或视图的文本内容

--Object_Name 指定存储过程、触发器或视图名称
exec sp_helptext @objname='Object_Name'

3、查询获取数据库中所有存储过程的列表

-- xtype  v:查询视图 P: 查询储过程 FN: 查询函数
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='P'

4、查询获取数据库表相关的所有存储过程

-- Table_Name 数据库表名
-- xtype  v:查询视图 P: 查询储过程 FN: 查询函数
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'

5、查询将重新生成数据库的所有索引

-- 用于对数据库中的所有用户表执行循环操作
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
-- 更新数据库中的统计信息
EXEC sp_updatestats
GO

6、查询获取返回在存储过程（如表、用户定义函数、其他存储过程等）中使用的所有对象名称

-- Ojbect_Name 存储过程名称
-- xtype  v:查询视图 P: 查询储过程 FN: 查询函数
;WITH stored_procedures AS (SELECT
oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P' AND o.name LIKE '%Ojbect_Name%')
SELECT Table_name FROM stored_procedures
WHERE row = 1

7、查询获取数据库的数据库表的大小

-- xtype  U:查询表
SELECT sob.name AS Table_Name,
SUM(sys.length) AS [Size_Table(Bytes)]
FROM sysobjects sysobj, syscolumns sys
WHERE sysobj.xtype='u' AND sys.id=sys

8、查询获取数据库中没有标识列的所有表

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE Table_NAME NOT IN
(
 SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
  INNER JOIN sys.identity_columns i ON (c.COLUMN_NAME=i.NAME)
  ) AND TABLE_TYPE ='BASE TABLE'

9、查询获取数据库上所有主键和外键的列表

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

10、查询获取指定表的主键和外键列表

-- Table_Name 指定表名称
SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'

11、重新设定所有表的 Identity 种子

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)'

12、查询获取具有数据行的表

-- 创建临时表
CREATE TABLE #tempTable (
Table_Name [varchar](max),
Total_Records int);
-- 查询统计有表的行数
EXEC sp_MSForEachTable @command1=' Insert Into #tempTable(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'
--
 SELECT * FROM #tempTable t ORDER BY t.Total_Records DESC;
 --
  DROP TABLE #tempTable;

13、查询获取 SQL Server 的当前语言

SELECT @@LANGUAGE AS 'Current_Language'

14、查询获取 SQL Server 的版本

SELECT @@VERSION AS 'Version_Name'

15、查询获取数据库服务器名称

SELECT @@SERVERNAME AS 'Server_Name'

16、查询获取当前用户进程的会话标识

SELECT @@SPID AS 'Session_Id'

17、查询获取当前设置的十进制和数字使用的精度级别

SELECT @@MAX_PRECISION AS 'MAX_PRECISION'

18、禁用所有表的所有约束

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

19、禁用表的指定触发器或全部触发器

-- Trigger_Name 触发器名称
-- Table_Name 表名称
ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name
-- 禁用全部触发器
ALTER TABLE Table_Name DISABLE TRIGGER ALL

20、启用表的指定触发器或全部触发器

-- Trigger_Name 触发器名称
-- Table_Name 表名称
ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name
-- 启用禁用全部触发器
ALTER TABLE Table_Name ENABLE TRIGGER ALL

21、禁用数据库所有触发器

EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

22、启用数据库所有触发器

EXEC sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"

23、查询获取硬盘的可用空间

EXEC master..xp_fixeddrives

24、查询获取最近修改的存储过程

-- type P:存储过程
-- N: 天数
SELECT name,modify_date
FROM sys.objects
WHERE type='P'
AND DATEDIFF(D,modify_date,GETDATE())< N

25、查询获取最近创建的存储过程

-- type P:存储过程
-- N: 天数
SELECT name,sys.objects.create_date
FROM sys.objects
WHERE type='P'
AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N

26、查询获取所有没有主键的表

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY Table_Name

27、查询获取所有没有外键的表

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0
ORDER BY Table_Name

28、查询获取所有没有标识列的表

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0
ORDER BY Table_Name

29、查询获取特定数据类型的所有列

-- Data_Type 数据类型名称 如 varchar int 
SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'Data_Type'

30、查询获取表的所有可 NULL 的列

-- Table_Name 表名称
SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.is_nullable=0 AND OBJECT_NAME(c.OBJECT_ID)='Table_Name'

31、查询获取包含视图的所有表

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

32、查询获取视图中使用的表的所有列

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAG

33、删除数据库中所有存储过程

Declare @Drop_sp Nvarchar(MAX)
Declare delete_Cursor Cursor For Select [name] From sys.objects where type = 'p'
Open delete_Cursor
Fetch Next From delete_Cursor Into @Drop_sp
While @@FETCH_STATUS= 0
Begin
Exec('DROP PROCEDURE ' + @Drop_sp)
Fetch Next From delete_Cursor Into @Drop_sp
End
Close delete_Cursor
Deallocate delete_Cursor

34、删除数据库中所有视图

Declare @Drop_View Nvarchar(MAX)
Declare drop_Cursor Cursor For Select [name] From sys.objects where type = 'v'
Open drop_Cursor
Fetch Next From drop_Cursor Into @Drop_View
While @@FETCH_STATUS = 0
Begin
Exec('DROP VIEW ' + @Drop_View)
Fetch Next From drop_Cursor Into @Drop_View
End
Close drop_Cursor
Deallocate drop_Cursor

35、删除数据库中所有表

EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'

36、查询获取表的列信息

-- Table_Name 表名称
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’

37、查询获取所有列包含任何约束

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

38、交换两列的值

-- Table_Name 表名称
-- Column1 第一列名
-- Column2 第二列名
UPDATE Table_Name SET Column1=Column2, Column2=Column1

39、获取当月和下个月的第一天

-- 获取当月的第一天
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),101) First_Date_Current_Month;
-- 获取下个月的第一天
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())+1),101) First_Date_Next_Month;

40、获取当月和上个月的最后日期

-- 获取当月的最后日期
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),101) Last_Date_Current_Month;
-- 获取上个月的最后日期
SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())),GETDATE()),101) Last_Date_Previous_Month;