حسین احمدی
حسین احمدی
بنیانگذار توسینسو و برنامه نویس و توسعه دهنده ارشد وب

معرفی 30 کوئری پرکاربرد در SQL Server که باید بشناسید

شما باید به عنوان یک متخصص پایگاه داده به این 30 مثال از Query های SQL مسلط باشید. در این مقاله قصد داریم تا با برخی از کوئری های تعریف شده در SQL Server که بعضاً بسیار مهم و پرکاربردی هستند آشنا شویم. به نظر من هر برنامه نویس و یا مدیر بانک اطلاعاتی باید با این کوئری ها آشنایی داشته باشد. این کوئری ها به شما کمک می کنند تا مشکلات خود را که از مسائل خیلی ساده تا مسائل پیچیده را شامل می شوند حل کنید. پس در این مطلب همراه من باشید.

دوره های شبکه، برنامه نویسی، مجازی سازی، امنیت، نفوذ و ... با برترین های ایران
برای مشاهده تخفیف های ویژه امروز کلیک کنید
سرفصل های این مطلب
  1. کوئری 1: دریافت لیست کلیه بانک های اطلاعاتی
  2. کوئری 2: نمایش متن یک Stored Procedure
  3. کوئری 3: دریافت لیست کلیه Stored Procedure های مربوط به یک Database
  4. کوئری 4: دریافت لیست Stored Procedure های مربوط به یک Table
  5. کوئری 5: Rebuild کردن همه Index های یک Database
  6. کوئری 6: دریافت لیست Object های استفاده شده در یک Stored Procedure
  7. کوئری 7: بدست آوردن تعداد Byte های مربوط به جداول
  8. کوئری 8: بدست آوردن لیست جداولی که ستون Identity ندارند
  9. کوئری 9: بدست آوردن لیست Primary Key ها و Foreign Key ها در یک Database
  10. کوئری 10: بدست آوردن لیست Primary Key ها و Foreign Key ها برای یک جدول خاص
  11. کوئری 11: RESEED کردن فیلدهای Identity در کلیه جداول بانک اطلاعاتی
  12. کوئری 12: بدست آوردن لیست کلیه جداول به همراه تعداد رکوردها
  13. کوئری 13: بدست آوردن نسخه Instance جاری
  14. کوئری 14: بدست آوردن زبان جاری
  15. کوئری 15: غیر فعال کردن کلیه Constraint ها در یک جدول
  16. کوئری 16: غیر فعال کردن کلیه Constraint ها بر روی همه جداول
  17. کوئری 17: بدست آوردن شناسه زبان جاری
  18. کوئری 18: بدست آوردن حداکثر دقت در نوع های numeric و decimal
  19. کوئری 19: بدست آوردن Server Name
  20. کوئری 20: بدست آوردن نام Instance جاری
  21. 21. بدست آوردن Session Id برای پراسس کاربر جاری
  22. 22. بدست آوردن فضای آزاد درایوها
  23. 23. غیرفعال کردن یک trigger
  24. 24. فعال کردن یک trigger
  25. 25. فعال کردن همه trigger های مربوط به یک جدول
  26. 27. غیر فعال کردن کلیه trigger های یک بانک اطلاعاتی
  27. 28. فعال کردن کلیه trigger های یک بانک اطلاعاتی
  28. 29. بدست آوردن لیست Stored Procedure های تغییر داده شده در N روز گذشته
  29. 30. لیست Stored Procedure هایی که در طول N روز گذشته ایجاد شده اند.

کوئری 1: دریافت لیست کلیه بانک های اطلاعاتی

این کوئری به شما کمک می کند که لیستی از بانک های موجود را دریافت کنید:

exec sp_helpdb

کوئری 2: نمایش متن یک Stored Procedure

این کوئری دستوراتی که برای یک SP نوشته شده را در خروجی برای شما نمایش می دهد:

exec sp_helptext @objname = 'Object_Name'

کافیست در بخش Object_Name نام SP مربوطه را بنویسید، برای مثال:

exec sp_helptext @objname = 'AddUser'

کوئری 3: دریافت لیست کلیه Stored Procedure های مربوط به یک Database

بوسیله کوئری زیر می توانید لیستی از Stored Procedure های تعریف شده برای یک بانک را بدست آورید:

USE [Database];
go

SELECT DISTINCT o.name, o.xtype    
FROM syscomments c    
INNER JOIN sysobjects o ON c.id=o.id    
WHERE o.xtype='P'

به جای Database، نام بانک مورد نظر خود را بنویسید. همچنین برای دریافت لیست View ها می توانید به جای p در کوئری بالا، از کاراکتر v و برای بدست آوردن Function از fn استفاده کنید.

کوئری 4: دریافت لیست Stored Procedure های مربوط به یک Table

بوسیله این کوئری می توان لیستی از Stored Procedure هایی که در آن از یک Table خاص استفاده شده را بدست آورد:

USE [Database];
go

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'

به جای Database نام بانک مورد نظر و به جای Table_Name نام جدول را بنویسید. همچنین برای دریافت لیست View ها می توانید به جای p در کوئری بالا، از کاراکتر v و برای بدست آوردن Function از fn استفاده کنید.

کوئری 5: Rebuild کردن همه Index های یک Database

ایندکس های موجود در بانک های اطلاعاتی، به مرور زمان و با درج و حذف های متعدد، با پدیده ای به نام fragmentation مواجه می شوند. بوسیله Query زیر می توان ایندکس های یک بانک را بازسازی کرد:

USE [Database];
GO

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"  
GO  
  
EXEC sp_updatestats  
GO

به جای Database نام بانک مورد نظر خود را بنویسید.

کوئری 6: دریافت لیست Object های استفاده شده در یک Stored Procedure

بوسیله این کوئری می توانید لیستی از Object های استفاده شده در یک Stored Procedure مانند جداول، توابع یا Stored Procedure های دیگر را بدست آورید:

;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 'SP_Name' )  
SELECT Table_name FROM stored_procedures  
WHERE row = 1

کافیست به جای SP_Name جان Stored Procedure مورد نظر را بنویسید.

کوئری 7: بدست آوردن تعداد Byte های مربوط به جداول

بوسیله این کوئری می تواند تعداد Byte های اشغال شده توسط هر جداول در یک Database را بر اساس Data Type های تعریف شده به دست آورید. دقت کنید که واحد Byte بر گردانده می شوند:

SELECT sob.name AS Table_Name,  
SUM(sys.length) AS [Size_Table(Bytes)]  
FROM sysobjects sob, syscolumns sys  
WHERE sob.xtype='u' AND sys.id=sob.id  
GROUP BY sob.name

کوئری 8: بدست آوردن لیست جداولی که ستون Identity ندارند

ستون های Identity ستوهایی هستند که مقدار آن ها به صورت پیش فرض توسط خود SQL و در زمان Insert کردن درج می شود، بوسیله این کوئری لیست جداولی که ستون Identity ندارند را به دست می آورید:

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 ic  
on  
(c.COLUMN_NAME=ic.NAME))  
AND  
TABLE_TYPE ='BASE TABLE'

کوئری 9: بدست آوردن لیست Primary Key ها و Foreign Key ها در یک Database

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

کوئری 10: بدست آوردن لیست Primary Key ها و Foreign Key ها برای یک جدول خاص

کوئری قبلی لیست کلید های کل بانک را برای بر میگرداند. کوئری شما 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'
GO

کوئری 11: RESEED کردن فیلدهای Identity در کلیه جداول بانک اطلاعاتی

بوسیله این کوئری می توانید فیلدهای Identity را در کلیه جداول RESEED کنید، یعنی عدد شروع را برای فیلدهای Identity مشخص کنید:

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

کوئری 12: بدست آوردن لیست کلیه جداول به همراه تعداد رکوردها

بوسیله این کوئری می توانید لیست کلیه جداول یک بانک اطلاعاتی را به همراه تعداد رکوردهای هر جدول بدست آورید:

CREATE TABLE #Tab  
(  
Table_Name [varchar](max),  
Total_Records int  
);  
EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'  
SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;  
DROP TABLE #Tab;

کوئری 13: بدست آوردن نسخه Instance جاری

SELECT @@VERSION AS Version_Name

خروجی بر روی سیستم من:

Microsoft SQL Server 2014 - 12.0.2269.0 (X64) 
	Jun 10 2015 03:35:45 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: ) (Hypervisor)

کوئری 14: بدست آوردن زبان جاری

SELECT @@LANGUAGE AS Current_Language;

خروجی بر روی سیستم من:

us_english

کوئری 15: غیر فعال کردن کلیه Constraint ها در یک جدول

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL

به جای Table_Name، نام جدول مورد نظر را بنویسید

کوئری 16: غیر فعال کردن کلیه Constraint ها بر روی همه جداول

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

کوئری 17: بدست آوردن شناسه زبان جاری

SELECT @@LANGID AS 'Language ID'

کوئری 18: بدست آوردن حداکثر دقت در نوع های numeric و decimal

SELECT @@MAX_PRECISION AS 'MAX_PRECISION'

کوئری 19: بدست آوردن Server Name

SELECT @@SERVERNAME AS 'Server_Name'

کوئری 20: بدست آوردن نام Instance جاری

SELECT @@SERVICENAME AS 'Service_Name'

21. بدست آوردن Session Id برای پراسس کاربر جاری

SELECT @@SPID AS 'Session_Id'

22. بدست آوردن فضای آزاد درایوها

EXEC master..xp_fixeddrives

23. غیرفعال کردن یک trigger

ALTER TABLE Table__Name DISABLE TRIGGER Trigger__Name

به جای Trigger__Name، نام trigger و به جای Table__Name نام جدول مورد نظر را بنویسید.

24. فعال کردن یک trigger

ALTER TABLE Table__Name ENABLE TRIGGER Trigger__Name

به جای Trigger__Name، نام trigger و به جای Table__Name نام جدول مورد نظر را بنویسید.

25. فعال کردن همه trigger های مربوط به یک جدول

ALTER TABLE Table__Name DISABLE TRIGGER ALL

به جای Table_Name نام جدول مورد نظر را بنویسید.

26. فعال کردن کلیه trigger های یک جدول

ALTER TABLE Table_Name ENABLE TRIGGER ALL

به جای Table__Name نام جدول مورد نظر را بنویسید.

27. غیر فعال کردن کلیه trigger های یک بانک اطلاعاتی

Use Database_Name  
  
Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

به جای Database__Name نام بانک مورد نظر را بنویسید.

28. فعال کردن کلیه trigger های یک بانک اطلاعاتی

Use Database_Name  
  
Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"

به جای Database__Name نام بانک مورد نظر را بنویسید.

29. بدست آوردن لیست Stored Procedure های تغییر داده شده در N روز گذشته

SELECT name,modify_date  
  
FROM sys.objects  
  
WHERE type='P'  
  
AND DATEDIFF(D,modify_date,GETDATE())< N

به جای P در شرط بالا، برای View ها کاراکتر V و برای توابع کاراکتر F را استفاده کنید. به جای N نیز تعداد روزهای مورد نظر را بنویسید.

30. لیست Stored Procedure هایی که در طول N روز گذشته ایجاد شده اند.

SELECT name,sys.objects.create_date  
  
FROM sys.objects  
  
WHERE type='P'  
  
AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N

به جای P در شرط بالا، برای View ها کاراکتر V و برای توابع کاراکتر F را استفاده کنید. به جای N نیز تعداد روزهای مورد نظر را بنویسید. در دوره آموزش SQL Server مهندس احمدی ، شما به خوبی با کوئری نویسی در SQL آشنا خواهید شد.

1 6
حسین احمدی
حسین احمدی

بنیانگذار توسینسو و برنامه نویس و توسعه دهنده ارشد وب

حسین احمدی ، بنیانگذار TOSINSO ، توسعه دهنده وب و برنامه نویس ، بیش از 12 سال سابقه فعالیت حرفه ای در سطح کلان ، مشاور ، مدیر پروژه و مدرس نهادهای مالی و اعتباری ، تخصص در پلتفرم دات نت و زبان سی شارپ ، طراحی و توسعه وب ، امنیت نرم افزار ، تحلیل سیستم های اطلاعاتی و داده کاوی ...

نظرات