前言
SQL是一種標(biāo)準(zhǔn)化的結(jié)構(gòu)化查詢語言，而SQL Server是使用較為流行的關(guān)系數(shù)據(jù)庫之一。了解 SQL 對軟件開發(fā)人員也非常重要。在 SQL Server 中，所有數(shù)據(jù)都使用 SQL 查詢進(jìn)行訪問和更新。本文將討論一些常用的 SQL 查詢，了解這些查詢可以解決一些復(fù)雜的任務(wù)。
常用查詢
1、查詢獲取所有數(shù)據(jù)庫的列表
2、查詢獲取數(shù)據(jù)庫指定存儲過程、觸發(fā)器或視圖的文本內(nèi)容
--Object_Name 指定存儲過程、觸發(fā)器或視圖名稱exec sp_helptext @objname='Object_Name'
3、查詢獲取數(shù)據(jù)庫中所有存儲過程的列表
-- xtype v:查詢視圖 P: 查詢儲過程 FN: 查詢函數(shù)SELECT DISTINCT o.name, o.xtypeFROM syscomments cINNER JOIN sysobjects o ON c.id=o.idWHERE o.xtype='P'
4、查詢獲取數(shù)據(jù)庫表相關(guān)的所有存儲過程
-- Table_Name 數(shù)據(jù)庫表名-- xtype v:查詢視圖 P: 查詢儲過程 FN: 查詢函數(shù)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、查詢將重新生成數(shù)據(jù)庫的所有索引
-- 用于對數(shù)據(jù)庫中的所有用戶表執(zhí)行循環(huán)操作EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"GO-- 更新數(shù)據(jù)庫中的統(tǒng)計(jì)信息EXEC sp_updatestatsGO
6、查詢獲取返回在存儲過程（如表、用戶定義函數(shù)、其他存儲過程等）中使用的所有對象名稱
-- Ojbect_Name 存儲過程名稱-- xtype v:查詢視圖 P: 查詢儲過程 FN: 查詢函數(shù);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、查詢獲取數(shù)據(jù)庫的數(shù)據(jù)庫表的大小
-- 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、查詢獲取數(shù)據(jù)庫中沒有標(biāo)識列的所有表
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、查詢獲取數(shù)據(jù)庫上所有主鍵和外鍵的列表
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、重新設(shè)定所有表的 Identity 種子
EXEC sp_MSForEachTable 'IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1DBCC CHECKIDENT (''?'', RESEED, 0)'
12、查詢獲取具有數(shù)據(jù)行的表
-- 創(chuàng)建臨時表CREATE TABLE #tempTable (Table_Name [varchar](max),Total_Records int);-- 查詢統(tǒng)計(jì)有表的行數(shù)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 的當(dāng)前語言
SELECT @@LANGUAGE AS 'Current_Language'
14、查詢獲取 SQL Server 的版本
SELECT @@VERSION AS 'Version_Name'
15、查詢獲取數(shù)據(jù)庫服務(wù)器名稱
SELECT @@SERVERNAME AS 'Server_Name'
16、查詢獲取當(dāng)前用戶進(jìn)程的會話標(biāo)識
SELECT @@SPID AS 'Session_Id'
17、查詢獲取當(dāng)前設(shè)置的十進(jìn)制和數(shù)字使用的精度級別
SELECT @@MAX_PRECISION AS 'MAX_PRECISION'
18、禁用所有表的所有約束
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
19、禁用表的指定觸發(fā)器或全部觸發(fā)器
-- Trigger_Name 觸發(fā)器名稱-- Table_Name 表名稱ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name-- 禁用全部觸發(fā)器ALTER TABLE Table_Name DISABLE TRIGGER ALL
20、啟用表的指定觸發(fā)器或全部觸發(fā)器
-- Trigger_Name 觸發(fā)器名稱-- Table_Name 表名稱ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name-- 啟用禁用全部觸發(fā)器ALTER TABLE Table_Name ENABLE TRIGGER ALL
21、禁用數(shù)據(jù)庫所有觸發(fā)器
EXEC sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
22、啟用數(shù)據(jù)庫所有觸發(fā)器
EXEC sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
23、查詢獲取硬盤的可用空間
EXEC master..xp_fixeddrives
24、查詢獲取最近修改的存儲過程
-- type P:存儲過程-- N: 天數(shù)SELECT name,modify_dateFROM sys.objectsWHERE type='P'AND DATEDIFF(D,modify_date,GETDATE())< N
25、查詢獲取最近創(chuàng)建的存儲過程
-- type P:存儲過程-- N: 天數(shù)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、查詢獲取所有沒有標(biāo)識列的表
SELECT name AS Table_NameFROM sys.tablesWHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0ORDER BY Table_Name
29、查詢獲取特定數(shù)據(jù)類型的所有列
-- Data_Type 數(shù)據(jù)類型名稱 如 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、刪除數(shù)據(jù)庫中所有存儲過程
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、刪除數(shù)據(jù)庫中所有視圖
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、刪除數(shù)據(jù)庫中所有表
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、獲取當(dāng)月和下個月的第一天
-- 獲取當(dāng)月的第一天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、獲取當(dāng)月和上個月的最后日期
-- 獲取當(dāng)月的最后日期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;
小結(jié)
以上列舉了40個常用的查詢，希望本文對您有所收獲，如有不到之處,請多多包涵。
該文章在 2024/12/9 18:41:54 編輯過
400 186 1886
