前言
SQL是一种标准化的结构化查询语言，而SQL Server是使用较为流行的关系数据库之一。了解 SQL 对软件开发人员也非常重要。在 SQL Server 中，所有数据都使用 SQL 查询进行访问和更新。本文将讨论一些常用的 SQL 查询，了解这些查询可以解决一些复杂的任务。
常用查询
1、查询获取所有数据库的列表
2、查询获取数据库指定存储过程、触发器或视图的文本内容
--Object_Name 指定存储过程、触发器或视图名称exec sp_helptext @objname='Object_Name'
3、查询获取数据库中所有存储过程的列表
-- xtype v:查询视图 P: 查询储过程 FN: 查询函数SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE o.xtype='P'
4、查询获取数据库表相关的所有存储过程
-- Table_Name 数据库表名-- xtype v:查询视图 P: 查询储过程 FN: 查询函数SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'
5、查询将重新生成数据库的所有索引
-- 用于对数据库中的所有用户表执行循环操作EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"GO-- 更新数据库中的统计信息EXEC sp_updatestatsGO
6、查询获取返回在存储过程（如表、用户定义函数、其他存储过程等）中使用的所有对象名称
-- Ojbect_Name 存储过程名称-- xtype v:查询视图 P: 查询储过程 FN: 查询函数;WITH stored_procedures AS (SELECToo.name AS table_name,ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS rowFROM sysdepends dINNER JOIN sysobjects o ON o.id=d.idINNER JOIN sysobjects oo ON oo.id=d.depidWHERE o.xtype = 'P' AND o.name LIKE '%Ojbect_Name%')SELECT Table_name FROM stored_proceduresWHERE row = 1
7、查询获取数据库的数据库表的大小
-- xtype U:查询表SELECT sob.name AS Table_Name,SUM(sys.length) AS [Size_Table(Bytes)]FROM sysobjects sysobj, syscolumns sysWHERE sysobj.xtype='u' AND sys.id=sys
8、查询获取数据库中没有标识列的所有表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE 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、查询获取数据库上所有主键和外键的列表
SELECTDISTINCTConstraint_Name AS [Constraint],Table_Schema AS [Schema],Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
10、查询获取指定表的主键和外键列表
-- Table_Name 指定表名称SELECTDISTINCTConstraint_Name AS [Constraint],Table_Schema AS [Schema],Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'
11、重新设定所有表的 Identity 种子
EXEC sp_MSForEachTable 'IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1DBCC 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_dateFROM sys.objectsWHERE type='P'AND DATEDIFF(D,modify_date,GETDATE())< N
25、查询获取最近创建的存储过程
-- type P:存储过程-- N: 天数SELECT name,sys.objects.create_dateFROM sys.objectsWHERE type='P'AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N
26、查询获取所有没有主键的表
SELECT name AS Table_NameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0ORDER BY Table_Name
27、查询获取所有没有外键的表
SELECT name AS Table_NameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0ORDER BY Table_Name
28、查询获取所有没有标识列的表
SELECT name AS Table_NameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0ORDER BY Table_Name
29、查询获取特定数据类型的所有列
-- Data_Type 数据类型名称 如 varchar int SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_NameFROM sys.columns AS cJOIN sys.types AS t ON c.user_type_id=t.user_type_idWHERE t.name = 'Data_Type'
30、查询获取表的所有可 NULL 的列
-- Table_Name 表名称SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_NameFROM sys.columns AS cJOIN sys.types AS t ON c.user_type_id=t.user_type_idWHERE 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_CursorFetch Next From delete_Cursor Into @Drop_spWhile @@FETCH_STATUS= 0BeginExec('DROP PROCEDURE ' + @Drop_sp)Fetch Next From delete_Cursor Into @Drop_spEndClose delete_CursorDeallocate delete_Cursor
34、删除数据库中所有视图
Declare @Drop_View Nvarchar(MAX)Declare drop_Cursor Cursor For Select [name] From sys.objects where type = 'v'Open drop_CursorFetch Next From drop_Cursor Into @Drop_ViewWhile @@FETCH_STATUS = 0BeginExec('DROP VIEW ' + @Drop_View)Fetch Next From drop_Cursor Into @Drop_ViewEndClose drop_CursorDeallocate drop_Cursor
35、删除数据库中所有表
EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'
36、查询获取表的列信息
-- Table_Name 表名称SELECT * FROM INFORMATION_SCHEMA.COLUMNSWHERE 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;
小结
以上列举了40个常用的查询，希望本文对您有所收获，如有不到之处,请多多包涵。
该文章在 2024/12/9 18:41:54 编辑过
400 186 1886
