شما باید به عنوان یک متخصص پایگاه داده به این 30 مثال از Query های SQL مسلط باشید. در این مقاله قصد داریم تا با برخی از کوئری های تعریف شده در SQL Server که بعضاً بسیار مهم و پرکاربردی هستند آشنا شویم. به نظر من هر برنامه نویس و یا مدیر بانک اطلاعاتی باید با این کوئری ها آشنایی داشته باشد. این کوئری ها به شما کمک می کنند تا مشکلات خود را که از مسائل خیلی ساده تا مسائل پیچیده را شامل می شوند حل کنید. پس در این مطلب همراه من باشید.
کوئری 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 آشنا خواهید شد.
بسیار موضوع جذابی هست برای امثال من که با دیتابیسها سروکار داریم
امیدوارم ادامه پیدا کنه
ممنون