SQL Server یکی از بهترین ابزارهای مدیریتی به نام SQL Server Management Studio (معروف به SSMS) را دارد. این ویژگی های زیادی را ارائه می دهد که زندگی توسعه دهندگان و DBA ها را بسیار آسان تر می کند. اما گاهی اوقات، برخی از مشکلات وجود دارد که با استفاده از SQL Server Management Studio قابل رفع نیست. به خصوص زمانی که نوبت به اجرای اسکریپت های SQL ad-hoc یا بازیابی نمونه های خراب SQL Server می رسد. در چنین مواردی می توانید از SQLCMD استفاده کنید.

این مقاله حمایت شده توسط Devart است. Devart در حال حاضر یکی از ارائه دهندگان پیشرو نرم افزار مدیریت پایگاه داده و راه حل های ALM برای محبوب ترین سرورهای پایگاه داده است.

شروع کار با SQLCMD

در این مقاله با SQLCMD آشنا می شویم. این یک ابزار خط فرمان است که می تواند برای کارهای زیر استفاده شود:

پرس و جوهای SQL ad-hoc و رویه های ذخیره شده را روی سرورهای محلی و راه دور اجرا کنید

خروجی پرس و جوی SQL را به فایل های متنی یا CSV صادر کنید

نمونه ها و پایگاه های داده SQL Server را در ویندوز و لینوکس مدیریت و مدیریت کنید

برای اینکه نوشتن پرس و جو در SSMS سریعتر و آسان تر شود، و همچنین برای بهبود آن با ویژگی های اضافی برای مدیریت و مدیریت پایگاه داده، آن را با dbForge SQL Tools، بسته ای از گفت نی ها که به طور یکپارچه در SSMS ادغام شده است، تقویت کردیم.

حالا اجازه دهید با نصب شروع کنیم.

برای نصب ابزار SQLCMD، باید هنگام نصب SQL Server ابزارهای کلاینت SQL Server را انتخاب کنید. همچنین می توانید آن را به طور جداگانه با استفاده از مدیر نصب SQL Server نصب کنید.

ابزار SQLCMD را می توان تنها با تایپ SQLCMD در PowerShell یا در خط فرمان فراخوانی کرد. با اجرای دستور زیر می‌توانید فهرست ی از گزینه‌های قابل استفاده با SQLCMD را مشاهده کنید:

 PS C:\Users\nisar> SQLCMD -?

این همان چیزی است که خروجی خط فرمان به نظر می رسد.

اتصال به SQL Server با استفاده از SQLCMD

اکنون، اجازه دهید نحوه اتصال به یک نمونه SQL Server با استفاده از SQLCMD را درک کنیم.

مثال 1: به نمونه پیش فرض SQL Server متصل شوید

برای اتصال به SQL Server در یک ماشین محلی، از دستور SQLCMD زیر استفاده کنید:

 C:\Users\nisar>sqlcmd -S Nisarg-PC

همانطور که می بینید، خروجی فرمان 1> است که نشان می دهد شما به SQL Server متصل هستید.

توجه داشته باشید که اگر به نمونه پیش‌فرض SQL Server در یک ماشین محلی متصل می‌شوید، مجبور نیستید نام میزبان/سرور را به صراحت مشخص کنید.

مثال 2: به یک نمونه SQL Server با نام متصل شوید

اکنون، اجازه دهید نمونه دیگری را تحلیل کنیم که نشان می دهد چگونه به یک نمونه SQL Server با نام متصل شویم.

برای اتصال به یک نمونه SQL Server با نام، باید پارامتر -S (نام سرور) را مشخص کنید، به عنوان مثال، اگر نام سرور شما MyServer و نمونه نامگذاری شده SQL2017 باشد، دستور اتصال به آن با استفاده از SQLCMD به این صورت خواهد بود:

 C:\>sqlcmd -S Nisarg-PC\SQL2019

در اینجا خروجی است.

مثال 3: اتصال به SQL Server با استفاده از احراز هویت ویندوز و احراز هویت SQL Server

حال، اجازه دهید نحوه اتصال به SQL Server با استفاده از احراز هویت ویندوز و SQL Server را ببینیم.

برای اتصال به SQL Server با استفاده از SQLCMD، می توانید از احراز هویت ویندوز و احراز هویت SQL Server استفاده کنید. اگر می خواهید از احراز هویت SQL Server استفاده کنید، باید گزینه های -U (نام کاربری) و -p (رمز عبور) را مشخص کنید. اگر رمز عبور را مشخص نکنید، ابزار SQLCMD از شما می خواهد که رمز عبور را وارد کنید. تصویر زیر این موضوع را نشان می دهد.

کار با SQLCMD در حالت تعاملی

در این قسمت نحوه اجرای SQLCMD در حالت تعاملی، اجرای کوئری های SQL و مشاهده خروجی را خواهیم دید. حالت تعاملی اجازه نوشتن دستورات و دستورات SQL را می دهد. بیایید با یادگیری نحوه اتصال به SQL Server، وارد شدن به حالت تعاملی و اجرای کوئری ها در SQLCMD شروع کنیم.

مثال 1: فهرستی از پایگاه های داده را با مالکان پر کنید

ابتدا با استفاده از دستور زیر به سرور پایگاه داده خود متصل شوید:

 C:\>sqlcmd -S Nisarg-PC -U sa -p

پس از شروع جلسه تعاملی، پرس و جوی SQL زیر را در ابزار SQLCMD اجرا کنید:

 use master; select a.name,b.name from sys.databases a inner join sys.server_principals b on a.owner_sid=b.sid where a.name not in ('ReportServer','ReportServerTempDB') and a.database_id>5;

در اینجا خروجی پرس و جو است.

همانطور که می بینید، پرس و جو فوق فهرست ی از پایگاه های داده را با صاحبان پایگاه داده پر کرده است.

مثال 2: پایگاه داده فعلی را تحلیل کنید

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

 Select DB_NAME() Go

این خروجی پرس و جو است.

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

مثال 3: پرس و جوهای SQL را اجرا کنید

شما می توانید پرس و جوهای SQL را با استفاده از SQLCMD با تعیین پارامتر -Q اجرا کنید. به عنوان مثال، شما می خواهید فهرست جداول ایجاد شده در پایگاه داده SchoolManagement را با استفاده از SQLCMD مشاهده کنید. دستور باید به صورت زیر نوشته شود:

 C:\>sqlcmd -S Nisarg-PC -d SchoolManagement -Q "select name from sys.tables"

به خروجی پرس و جو نگاهی بیندازید.

به طور مشابه، می توانید پرس و جوهای دیگر را نیز اجرا کنید. لطفاً توجه داشته باشید که لاگینی که برای اتصال به SQL Server استفاده می‌کنید باید دارای مجوز لازم در پایگاه داده باشد.

کار با SQLCMD در خط فرمان

اینجاست که نحوه اجرای اسکریپت های SQL را از طریق خط فرمان خواهیم دید. این ویژگی زمانی مفید است که می‌خواهید کارهای اتوماسیون، عملیات انبوه و پرس‌وجوهای طولانی‌مدت که نیازی به ورودی کاربر ندارند را اجرا کنید.

من یک اسکریپت SQL ایجاد کرده ام که حاوی یک پرس و جوی SQL است که برای پر کردن فهرست اشیاء ایجاد شده در پایگاه داده WideWorldImporters استفاده می شود. پرس و جو به شرح زیر است:

 use [WideWorldImporters] go select name, type_desc, create_date from sys.objects where type_desc <>'SYSTEM_TABLE'

کوئری فوق را به یک اسکریپت SQL با نام sp_get_db_objects.sql اضافه کنید. حال اجازه دهید خروجی را به یک فایل متنی با نام database_objects.txt صادر کنیم.

برای آن، از گزینه های زیر استفاده خواهیم کرد:

-o: فایل مقصد را مشخص کنید. در این نسخه نمایشی، فایل متنی مقصد WideWorldImportores_objects.txt نام دارد.

-i: محل اسکریپت SQL را مشخص کنید. در این نسخه نمایشی، اسکریپت SQL DBObjects.sql نامیده می شود.

حالا بیایید دستور زیر را اجرا کنیم:

 sqlcmd -S Nisarg-PC -i D:\Scripts\DBObjects.sql -o D:\Scripts\WideWorldImportores_objects.txt

پس از اتمام موفقیت آمیز دستور، نوبت به تحلیل فایل متنی می رسد.

همانطور که در تصویر بالا مشاهده می کنید، پرس و جو با موفقیت اجرا شده است.

حالا بیایید مثال دیگری بزنیم. در اینجا ما یاد خواهیم گرفت که چگونه با استفاده از یک اسکریپت SQL یک نسخه پشتیبان از StackOverflow2010 تولید کنیم. پرس و جو برای ایجاد یک نسخه پشتیبان به شرح زیر است:

 use master go backup database [Stackoverflow2010] to disk ='D:\SQLBackups\Stackoverflow2010.bak' with compression, stats=5

من فرمان پشتیبان گیری را در یک اسکریپت SQL به نام StackOverflow2010_backup_script.sql ذخیره کرده ام. برای اجرای اسکریپت، دستور SQLCMD به صورت زیر خواهد بود:

اسکرین شات 1:

همانطور که در تصویر بالا مشاهده می کنید، نسخه پشتیبان تهیه شده است.

اسکرین شات 2:

استفاده از SQLCMD در SQL Server Management Studio

برای استفاده از SQLCMD در SSMS، ابتدا باید حالت SQLCMD را فعال کنید. برای انجام این کار، Query را از منو انتخاب کنید و حالت SQLCMD را انتخاب کنید، همانطور که در تصویر زیر نشان داده شده است:

اگر می خواهید حالت SQLCMD را به طور پیش فرض تنظیم کنید، به Tools → Options بروید. در گزینه ها ، Query execution → SQL Server → General را انتخاب کنید و کادر به طور پیش فرض، باز کردن پرسش های جدید در حالت SQLCMD را انتخاب کنید.

حالا بیایید نحوه استفاده از آن را ببینیم.

به عنوان مثال، من می خواهم کل رکوردهای جدول Posts پایگاه داده Stackoverflow2010 را دریافت کنم. برای انجام این کار، کوئری باید به صورت زیر نوشته شود:

 :SETVAR TABLENAME "Posts" :SETVAR DATABASENAME "Stackoverflow2010" use $(DATABASENAME); select count(1) from $(TABLENAME); GO

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

حال، اجازه دهید نحوه استفاده از SQLCMD در PowerShell را ببینیم.

استفاده از SQLCMD در PowerShell

می توانید SQLCMD را با استفاده از PowerShell فراخوانی کنید. برای انجام این کار، باید PowerShell را برای SQL Server نصب کنید. برای آشنایی بیشتر با PowerShell for SQL Server و نحوه نصب آن می توانید این مقاله را بخوانید.

بگذارید یک مثال ساده بزنیم. فرض کنید من می خواهم فهرست رویه های ذخیره شده پایگاه داده WideWorldImporters را دریافت کنم. دستور PowerShell به صورت زیر است:

 PS C:\WINDOWS\system32> invoke-sqlcmd -database wideworldimporters -query "select name from sys.procedures"

در اینجا خروجی است.

مثال دیگری نشان می دهد که چگونه می توان خروجی یک اسکریپت SQL را با استفاده از sqlps به یک فایل متنی صادر کرد. فرض کنید قصد داریم فهرست ی از کارهای عامل SQL Server را صادر کنیم. من یک اسکریپت به نام SQLJobs.sql ایجاد کرده ام که فهرست کارهای SQL را بازیابی می کند. اسکریپت حاوی دستور T-SQL زیر است:

 use [msdb] go select name, description,date_created from Sysjobs

برای اجرای اسکریپت، دستور زیر را در PowerShell برای SQL Server اجرا می کنم.

 invoke-sqlcmd -inputfile "D:\Scripts\SQLJobs.sql" | Out-File -FilePath "D:\Scripts\SQLJobs_List.txt"

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

تکنیک های پیشرفته SQLCMD

در اینجا چند تکنیک پیشرفته وجود دارد که می تواند به شما در استفاده موثرتر از SQLCMD کمک کند. من قصد دارم آنها را با مثال های ساده توضیح دهم.

مثال 1: نمایش پیام های خطا با توجه به سطح شدت خطا

این مثال نحوه نمایش یک پیام خطا را با توجه به میزان شدت آن نشان می دهد. این روش با گفت ن گزینه -m قابل استفاده است. فرض کنید در حال اجرای یک کوئری SELECT در برابر یک شی پایگاه داده غیر موجود هستید. دستور "شیء نامعتبر" را برمی گرداند و سطح شدت آن خطا 16 است. اسکرین شات زیر را ببینید.

بیایید نگاهی به خطایی بیندازیم که سطح شدت آن 15 است - یک خطای نحوی

همانطور که در تصویر بالا مشاهده می کنید، شدت خطا 15 است، پس SQLCMD هیچ خطایی را نشان نداد.

مثال 2: در صورت بروز خطا از جلسه SQLCMD خارج شوید

این مثال نحوه خروج از جلسه SQLCMD را هنگامی که یک فرمان یا پرس و جو با خطا مواجه می شود را نشان می دهد. برای این کار باید گزینه -b را مشخص کنید. فرض کنید می خواهید از SQLCMD خارج شوید زمانی که پرس و جو با خطای «پایگاه داده وجود ندارد» مواجه شد.

مثال 3: ورودی کاربر را بپذیرید

این مثال نحوه پذیرش ورودی کاربر را هنگام اجرای اسکریپت T-SQL نشان می دهد. این شامل اسکریپت کردن متغیرها در SQLCMD است. برای نشان دادن آن، من اسکریپتی ایجاد کرده ام که نام رسمی کشور را پر می کند. این اسکریپت از پایگاه داده WideWorldImporters و جدول application.Countries استفاده می کند. محتوای فیلمنامه به شرح زیر است:

 use [WideWorldImporters] Go select CountryName, FormalName from application.countries where CountryName=$(CountryName) Go

اکنون اسکریپت را ذخیره کرده و با استفاده از دستور SQLCMD زیر اجرا می کنم:

 sqlcmd -S Nisarg-PC -v CountryName='India' -i D:\Scripts\Asia_Countries.sql

در اینجا خروجی است.

همانطور که می بینید، پرس و جو نام رسمی هند را برگرداند.

نتیجه

در این مقاله با مثال های مختلف با دستور SQLCMD و نحوه استفاده از آن آشنا شدید. SQLCMD ابزار قدرتمندی است که می تواند به شما در اجرای اسکریپت ها، صادرات خروجی به فایل های مختلف و مدیریت SQL Server کمک کند. همچنین می توانید از DAC (اتصال اختصاصی مدیریت) استفاده کنید که به دسترسی به سرورهای پایگاه داده آسیب دیده یا خراب کمک می کند.

در نهایت، همیشه می‌توانید قابلیت‌های موجودی SSMS را با تکمیل و قالب‌بندی هوشمند کد، کنترل منبع، آزمایش واحد، اتوماسیون خط فرمان و بسیاری از موارد مفید دیگر در بسته‌هایی مانند ابزارهای dbForge SQL تقویت کنید.

کاربران جدید می توانند یک تست درایو رایگان به مدت 30 روز به آنها بدهند. پس از نصب بسته نرم افزاری، همه افزونه ها به راحتی از منوی SSMS و Object Explorer در دسترس خواهند بود. آنقدر در وقت من صرفه جویی می کنند که نمی توانم آنها را توصیه نکنم.

خبرکاو