Archive

Archive for the ‘Management Studio’ Category

SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

April 7, 2014 Leave a comment

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on.

SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use.

To enable this option follow below steps.

Step1: Go to Tools > Options

image

Step2: Navigate to Environment > Startup

image

Step3: Check Hide system objects in Object Explorer and click OK:

image

Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue:

image

 

Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in Object Explorer:

image

As you can see from above screenshot that System databases node is no longer available in Object Explorer.

What is hidden by this setting:

1. System databases – This is not hidden for SQL Server 2012
2. System Stored Procedures
3. System Tables
4. System Views

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Displaying line numbers in Query Editor – SSMS

January 20, 2014 Leave a comment

You can enable line numbers to be displayed in SSMS Query Editor. This is extremely useful when working on a large module.

To enable line numbers in Query Editor windows, follow below steps:

Step1: Go to Tools > Options

image

Step2: In the Options dialog box navigate to Text Editor > Transact-SQL > General

image

Step 3: Check “Line Numbers” and click on “OK”

image

Now, when a query window is opened Line Numbers will be displayed:

image

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to change SQL Server ERRORLOG location

July 8, 2013 3 comments

By default SQL Server ERRORLOG is stored in "C:\Program Files\Microsoft SQL Server\InstanceFolder\MSSQL\Log" folder. The ERRORLOG location is configured as a startup parameter for SQL Server Service.

image

To change the location of ERRORLOG you need to modify the startup parameter -e.

For example, if you need to move logs to C:\Logs\ then replace the startup parameter as below:

Step 1:

Replace "-eC:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\ERRORLOG"

to "-eC:\Logs\ERRORLOG"

Step 2:

Restart SQL Server Service to apply changes. The ERRORLOG will be created to "C:\Logs" after SQL Server is restarted.

 

To change location for SQL Server Agent Logs:

Location for SQL Server Agent Log can be changed using T-SQL or SQL Server Management Studio.

Method 1: To change SQL Server Agent Log location right click on "Error Logs" node and select "Configure"

image

Provide new location for Log file in "Error Log File" path:

image

Click on "OK" and restart SQL Server Agent to apply changes.

Method 2: To change log location using T-SQL

To change log location using T-SQL you can use below code:

USE [msdb]

GO

EXEC   msdb.dbo.sp_set_sqlagent_properties

       @errorlog_file = N'C:\Logs\SQLAGENT.OUT'

GO

Execute this code and restart SQL Server agent to apply changes.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to Detach a Database

June 3, 2013 Leave a comment

To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.

There are two different methods available to detach a database from a server.

Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:

To detach a database using SQL Server Management Studio:

1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.

image

2. Check the "Drop Connections" checkbox and click "OK" to detach the database

image

Now the database is detached from server and you can move .mdf and .ldf files related to database.

 

Method 2: Detach database using T-SQL:

To detach a database from a server using T-SQL you can use below code:

USE [master]

GO

 

ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'

GO

Result Set:

Command(s) completed successfully.

This detaches database from server.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Filtering objects in Object Explorer – Management Studio

May 28, 2013 3 comments

When you are working on a database with a large number of objects sometimes it becomes a challenge to locate objects. Object Explorer in SQL Server Management Studio lists all objects according to category and it may be hard to locate specific object.

To resolve this you can Object Explorer provides filtering for objects. You can apply a filter in Object Explorer for common object categories. i.e. Table, Views, Stored Procedures etc.

For example, if we need to locate a stored procedure which has "Sales" in name we can use Object Explorer filtering as below:

Step 1: Right Click on Stored Procedures category in required database and select Filter > Filter Settings:

image

Step 2: In the Filter Settings dialog box, specify "Sales" in Value field, and click on OK to apply filter:

image

After applying this filer Object Explorer will only show stored procedures matching the specified filter criteria, only those which have word "Sales" in their name in this case:

image

Step 3: To clear the filter and list all objects again, right click on filtered category and select Filter > Remove Filter. This will clear the filter applied.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Show/Hide Results Pane in Management Studio 2012

May 20, 2013 5 comments

In earlier version of SQL Server Management Studio (2005, 2008 and 2008 R2) you can show/hide results pane using keyboard shortcut Ctrl+R. This shortcut was also present in Query Analyzer (SQL Server 2000).

This shortcut is no longer available in SQL Server Management Studio 2012. In this version you need to use Ctrl+Shift+Alt+R to show/hide query results pane. Another alternative shortcuts you can use to show or hide results pane is Alt+WS and Alt+WI respectively. I find the later to be more easy to use.

If you have gotten used to using old shortcut Ctrl+R you can customize shortcuts in SQL Server Management Studio.

To change keyboard shortcut for show/hide results pane:

Step 1: Go to Tools > Options in SQL Server Management Studio:

image

Step 2: Navigate to Environment > Keyboard > Keyboard

image

Step 3: Search for Window.ShowResultsPane using Show commands containing: textbox.

image

Step 4:

(1) Select Ctrl+Shift+Alt+R (SQL Query Editor) in Shortcuts for selected command: list.
(2) Select SQL Query Editor in Use new shortcut in: list.
(3) Go to Press shortcut keys: textbox and press Ctrl+R or any other shortcut you need.
(4) Click on Assign.
(5) Click on OK to apply changes.

image

Step 5: You need to restart SQL Server Management Studio to apply these changes.

 

Note: The Window menu will still show Ctrl+Shift+Alt+R as shortcut we have only added an additional shortcut, we have not removed existing shortcut.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – How to find Default data and log path for SQL Server 2012

May 6, 2013 1 comment

To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')

SELECT [Default Log Path]  = SERVERPROPERTY('InstanceDefaultLogPath')

GO

Result Set:

Default Data Path

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\

 

(1 row(s) affected)

 

Default Log Path

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\

 

(1 row(s) affected)

You can also get this information from Server Properties dialog box in SQL Server Management Studio.

To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.

 

Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Follow

Get every new post delivered to your Inbox.

Join 264 other followers