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

[ali]

لیست رکوردهای جدولهای پایگاه داده ، تمامی رکوردهای همه جدولهای پایگاه داده ، بدست آوردن تمامی رکوردهای همه جدولهای پایگاه داده
نکته شماره 1: برگرداندن تعداد رکوردهای موجود در تمامی جدولهای پایگاه داده با یک حرکت
گاهی اوقات نیاز داریم که تعداد رکوردهای موجود در هر یک از جدولها را در لیستی مرتب شده ببینیم. منظور این است که با اجرای یک پرس و جو بتوانیم در یک جدول اطلاعات مربوط به نام جدول و تعداد رکوردهای موجود در آن جدول را مشاهده کنیم. با استفاده از رویه ذخیره شده با عنوان sp_MSForEachTable می توان به این خواسته دست یافت:

کد:

CREATE TABLE #tblCounts -- ایجاد جدول مجازی
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #tblCounts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?' -- اطلاعات در این بخش واکشی  و درج می شوند
SELECT table_name, row_count FROM #tblCounts ORDER BY  row_count DESC-- نمایش در این بخش انجام می شود

DROP TABLE #tblCounts -- حذف جدول مجازی

[ali]

در برخی موارد لازم می شود که مالک جدول پایگاه داده اس کیو ال سرور را تعویض کنیم. منظور همان table owner می باشد. برای این منظور از کد زیر استفاده می نمائیم:

کد:

EXEC sp_changeobjectowner 'userlast.tblmainID', 'dbo'

در این مثال کاربر جدول tblmainID از userlast به عبارت dbo تغییر می نماید.

[ali]

در ابتدا باید این نکته را عرض کنم که SPID در حقیقت مخفف Server Process ID می باشد که به هر کاربری که به SQL متصل می شود یک کد یکتا توسط SQl Server اختصاص داده می شود و توسط این ID می توان در بخش SQL Server Profiler تراکنشهای کاربر را مشاهده نمود.

شکل کلی دستور:

کد:

SELECT @@SPID AS 'SPID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

میتوان Stored Procedure نوشت و از طریق آن در متن برنامه SPID و یک سری اطلاعات دیگر را بدست آورد:

کد:

-- ================================================================================​==========
-- Entity Name:    sp_SPID_LoginName_UserName
-- Author:    A. Najafzadeh (+98)9131253620
-- Create date: 1393-01-11    -     03/31/2014 05:07:22 ب.ظ
-- Description:    This stored procedure is intended for SpID And Login Nasme And User name
-- ================================================================================​==========
ALTER Procedure [dbo].[sp_SPID_LoginName_UserName]
As
Begin
SELECT @@SPID AS 'SPID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
End

و این هم یک تکه از کد سی شارپ که می توان از طریق آن SPID را در متن برنامه به دست آورد. ضمنا در این کد این گونه فرض شده که از LingToSqlDataSource استفاده شده و SP نوشته شده در آن Add شده است:

کد:

LSC_PdfProjDataContext db = new LSC_PdfProjDataContext(); // دریافت SPID
            var Query = from Row in db.sp_SPID_LoginName_UserName()
                        select new
                        {
                            Row.SPID,
                            Row.Login_Name,
                            Row.User_Name
                        };
            foreach (var Q in Query)
            {
                Tlbl_SpID.Text = "  |  SPID = " + Q.SPID.ToString().Trim();
            }

[ali]

برای بدست آوردن نام Server در SQL Server از کد زیر استفاده می نمائیم:

کد:

select @@SERVERNAME

[ali]

منظور از instance name در حقیقت همان نام Server هایی است که SQL Server برای اتصال به آنها می تواند استفاده کند. در زیر لینک یک برنامه مفید مشاهده می شود که برای بدست آوردن Instance Name های نصب شده در یک کامپیوتر و مجوعا در یک شبکه محلی استفاده می شود:

لینک دانلود برنامه

همانطور که در شکل مشاهده می شود Instance Name هایی که در سیستم موجود است در لیست نمایش داده شده است. محل استفاده از Instance Name در حقیقت در زمان Connect شدن به Server است و این نام را باید در بخش Server Name قرار داد. (شکل زیر)

[ali]

فرض کنید یک جدول دارید که دارای یک فیلد از نوع اتو نامبر می باشد. همانطور که می دانید اگر یک رکورد در جدولی که دارای فیلد اتونامبر است حذف شود ، نمی توان به صورت دستی بین دو رکورد و به جای رکورد حذف شده رکوردی را درج کرد.
SQL Server دارای دستورهایی است که می توان این کار را توسط کد نویسی انجام داد. جدول زیر را در نظر بگیرید: (شکل زیر)

در این جدول فیلد FactorID از نوع autonumber می باشد. با دستور Select رکوردهای موجود در جدول را واکشی می نماییم. (شکل زیر)

در این مرحله می خواهیم بین رکوردهای 1 و 12 یک رکورد با FactorID شماره 5 درج کنیم. برای این منظور کد زیر را نوشته و آن را اجرا می نمائیم:

کد:

SET IDENTITY_INSERT tblFactors ON
    insert into tblFactors
    (FActorID , OrderID , FactorNumber , AcceptState)
    values (5 , 50 , 'Me-1321231' , 0 );
SET IDENTITY_INSERT tblFactors OFF

پس از اجرا ، یک رکورد به جدول اضافه می گردد. (شکل زیر)

!نکته: برای اینکه بتوانید رکورد درج نمایید بایستی از تمامی فیلدها در پرس و جو استفاده نمایید. در صورتی که یک فیلد از قلم بیفتد پرس و جوی نوشته شده به درستی اجرا نخواهد شد.

[ali]

در بعضی از مواقع ، برنامه نویسها و یا کاربران پایگاه داده نیاز به این دارند که آخرین آی دی مربوط به فیلد Auto number (که از نوع عددی و یکتا می باشد) رکورد ایجاد شده را در همان لحظه درج استخراج کنند و از آن در متن برنامه استفاده کنند. چندین روش برای این کار وجود دارد. یکی از روشها استفاده از متغیر زیر (که از متغیرهای موجود در SQL Server می باشد) است:

کد:

@@IDENTITY

برای این منظور می توان از کدهای زیر استفاده نمود:

کد:

Insert Into tblBooks  ([BookName],[BookGroupID]) Values ('test',1) -- دستور درج در پایگاه داده
Declare @ReferenceID int                                           -- تعریف متغیر  
SET @ReferenceID = @@IDENTITY                                      -- مقدار دهی متغیر
select @ReferenceID                                                -- نمایش مقدار

و یا اینکه می توان دستورهای فوق را در قالب یک رویه ذخیره شده (Stored Procedure) استفاده کرد:

کد:

Create Procedure sp_tblBooks_Insert
    @BookName nvarchar(250),
    @BookGroupID decimal
As
Begin
    Insert Into tblBooks
        ([BookName],[BookGroupID])
    Values
        (@BookName,@BookGroupID)
    Declare @ReferenceID int
    Select @ReferenceID = @@IDENTITY
    Return @ReferenceID
End
GO

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

کد:

CREATE TABLE [dbo].[tblBooks]
    [BookID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
    [BookName] [nvarchar](250) NOT NULL,
    [BookGroupID] [decimal](18, 0) NOT NULL

[ali]

با استفاده از این روش شما می توانید عنوان فیلد را به صورت پارامتر به سمت SQL Server ارسال نمایید و نتیجه را دریافت نمایید.

کد:

DECLARE @value varchar(10)  
SET @value = 'intStep'  
DECLARE @sqlText nvarchar(1000);

SET @sqlText = N'SELECT ' + @value + ' FROM dbo.tblBatchDetail'
Exec (@sqlText)

[ali]

به جای tblName عنوان جدول مورد نظر را وارد نمایید.

کد:

select *    
  from information_schema.columns  
where table_name = 'tblName'

[ali]

کد:

select REPLACE(REPLACE(CONVERT(nvarchar(30), GETDATE(),9) , ':' , '') , ' ' , '')