نکته های مفید و حرفه ای در اس کیو ال سرور SQL Server

[ali]

برای بدست آوردن عنوان کلیدهای اصلی و کلیدهای خارجی و ارتباط بین آنها در جدولها از کوئری زیر استفاده میشود:
Display Database Table Foreign Key Relationships

کد:

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
--WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
--WHERE PK.TABLE_NAME IN ('one_thing', 'another')
--WHERE FK.TABLE_NAME IN ('one_thing', 'another')

[ali]

میزان فضای اشغال شده توسط ساختار هر جدول بر اساس بایت توسط کوئری زیر به دست می آید:

کد:

SELECT CASE WHEN (GROUPING(sob.name)=1) THEN 'All_Tables'
   ELSE ISNULL(sob.name, 'unknown') END AS Table_name,
   SUM(sys.length) AS Byte_Length
FROM sysobjects sob, syscolumns sys
WHERE sob.xtype='u' AND sys.id=sob.id
GROUP BY sob.name
WITH CUBE

[ali]

برای بدست آوردن اطلاعات مفیدی از جدولهای پایگاه داده و نمایش تعداد رکوردهای هر جدول و مقدار فضایی که اطلاعات موجود در هر جدول در پایگاه داده اشغال کرده از کوئری زیر استفاده می شود (با استفاده از این کوئری می توان حجیم ترین جدول را مشاهده کرد):

کد:

CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

[ali]

ایجاد کرسر در SQL

کد:

DECLARE @AccountID INT
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT AssetsRowID
FROM dbo.tblAssets
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountID

[ali]

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

کد:

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tblAssetFinishedPrice%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tblAssetFinishedPrice%'

[ali]

کلید اصلی یا Primary Key بر روی یک [یا چند] ستون با مقدار منحصر به فرد برای هر فیلد ایجاد میشود. کلید اصلی یک شاخص یا clustered index بر روی فیلد مورد نظر ایجاد می کند و ضمنا اینکه کلید اصلی مقدار NULL قبول نمی کند. برای ایجاد کلید اصلی از کد زیر استفاده می شود:

کد:

CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO
--Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

کلید یکتا یا Unique Key بر روی یک [یا چند] ستون با مقدار منحصر به فرد برای هر فیلد ایجاد میشود. کلید یکتا یک شاخص غیر خوشه ای یا non-clustered index بر روی فیلد یا فیلدهای مورد نظر ایجاد می کند و ضمنا اینکه کلید یکتا فقط یک مقدار NULL قبول می کند. برای ایجاد کلید یکتا از کد زیر استفاده می شود:

کد:

ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO

ضمنا هر ایندکس حداکثر می تواند 900 بایت (از نظر ساختار) باشد. در غیر اینصورت پیغام خطایی از طرف سیستم مشاهده خواهید کرد.

[ali]

با استفاده از کوئری زیر می توانید تمامی تریگرهای موجود در پایگاه داده را یافته و اطلاعاتی در مورد آنها بدست آورید.

کد:

SELECT  table_name = OBJECT_NAME(parent_object_id) ,
        trigger_name = name ,
        trigger_owner = USER_NAME(schema_id) ,
        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
          WHEN 1 THEN 'Disabled'
          ELSE 'Enabled'
        END AS status
FROM    sys.objects
WHERE   type = 'TR'
ORDER BY OBJECT_NAME(parent_object_id)

[ali]

کد:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')