شما باید به عنوان یک متخصص پایگاه داده به این 30 مثال از Query های SQL مسلط باشید. در این مقاله قصد داریم تا با برخی از کوئری های تعریف شده در SQL Server که بعضاً بسیار مهم و پرکاربردی هستند آشنا شویم. به نظر من هر برنامه نویس و یا مدیر بانک اطلاعاتی باید با این کوئری ها آشنایی داشته باشد. این کوئری ها به شما کمک می کنند تا مشکلات خود را که از مسائل خیلی ساده تا مسائل پیچیده را شامل می شوند حل کنید. پس در این مطلب همراه من باشید.
این کوئری به شما کمک می کند که لیستی از بانک های موجود را دریافت کنید:
exec sp_helpdb
این کوئری دستوراتی که برای یک SP نوشته شده را در خروجی برای شما نمایش می دهد:
exec sp_helptext @objname = 'Object_Name'
کافیست در بخش Object_Name نام SP مربوطه را بنویسید، برای مثال:
exec sp_helptext @objname = 'AddUser'
بوسیله کوئری زیر می توانید لیستی از 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 استفاده کنید.
بوسیله این کوئری می توان لیستی از 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 استفاده کنید.
ایندکس های موجود در بانک های اطلاعاتی، به مرور زمان و با درج و حذف های متعدد، با پدیده ای به نام fragmentation مواجه می شوند. بوسیله Query زیر می توان ایندکس های یک بانک را بازسازی کرد:
USE [Database];
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO
به جای Database نام بانک مورد نظر خود را بنویسید.
بوسیله این کوئری می توانید لیستی از 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 مورد نظر را بنویسید.
بوسیله این کوئری می تواند تعداد 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
ستون های 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'
SELECT DISTINCT Constraint_Name AS [Constraint], Table_Schema AS [Schema], Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE GO
کوئری قبلی لیست کلید های کل بانک را برای بر میگرداند. کوئری شما 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
بوسیله این کوئری می توانید فیلدهای Identity را در کلیه جداول RESEED کنید، یعنی عدد شروع را برای فیلدهای Identity مشخص کنید:
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
بوسیله این کوئری می توانید لیست کلیه جداول یک بانک اطلاعاتی را به همراه تعداد رکوردهای هر جدول بدست آورید:
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;
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)
SELECT @@LANGUAGE AS Current_Language;
خروجی بر روی سیستم من:
us_english
ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL
به جای Table_Name، نام جدول مورد نظر را بنویسید
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
SELECT @@LANGID AS 'Language ID'
SELECT @@MAX_PRECISION AS 'MAX_PRECISION'
SELECT @@SERVERNAME AS 'Server_Name'
SELECT @@SERVICENAME AS 'Service_Name'
SELECT @@SPID AS 'Session_Id'
EXEC master..xp_fixeddrives
ALTER TABLE Table__Name DISABLE TRIGGER Trigger__Name
به جای Trigger__Name، نام trigger و به جای Table__Name نام جدول مورد نظر را بنویسید.
ALTER TABLE Table__Name ENABLE TRIGGER Trigger__Name
به جای Trigger__Name، نام trigger و به جای Table__Name نام جدول مورد نظر را بنویسید.
ALTER TABLE Table__Name DISABLE TRIGGER ALL
به جای Table_Name نام جدول مورد نظر را بنویسید.
26. فعال کردن کلیه trigger های یک جدول
ALTER TABLE Table_Name ENABLE TRIGGER ALL
به جای Table__Name نام جدول مورد نظر را بنویسید.
Use Database_Name Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
به جای Database__Name نام بانک مورد نظر را بنویسید.
Use Database_Name Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
به جای Database__Name نام بانک مورد نظر را بنویسید.
SELECT name,modify_date FROM sys.objects WHERE type='P' AND DATEDIFF(D,modify_date,GETDATE())< N
به جای P در شرط بالا، برای View ها کاراکتر V و برای توابع کاراکتر F را استفاده کنید. به جای 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 آشنا خواهید شد.