SQL SERVER – Find Anything in Object Explorer in SSMS

This blog post is a follow up post of an excellent write up by my friend Vinod Kumar. I suggest you read his blog post here SSMS Tips–Object Explorer Details. After reading his blog post, I called him up and had an interesting conversation with him. Here is our conversation:

Pinal: It is easy to find anything in Object Explorer but it is not possible to find everything in Object Explorer.

Vinod: What do you mean? Explain me with example.

Pinal: For example, if I want to find anything related to any schema or table, I can go to Object Explorer and can search about that schema and find relevant information.

However, if I want every single object where any string exists, how do I find all of those objects.

Vinod: That is easy as well, remember SQL Server is Microsoft product and will follow the simple rules of the search which you are familiar. Just try to wrap your string with % and see the magic.

Pinal: Never thought of it. Let me try.

After the conversation was over, I tried to wrap my search string with % and it works, indeed it works. SSMS retrieved every single object where the string appeared.

Thanks Vinod. It is so cool that when we have questions we can reach out to experts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Rename a Table Name Containing [ or ] Identifier in the Name – Part 2

Yesterday I posted article where we discussed how to rename a table name where identifier is part of the tablename. Read the blog post Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name. If the table name contains an identifier, it is not easy to rename a table, the default method will show an error as displayed below.

sp_rename '[]', 'ProjectA';

The above query will give us following error:

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107
Syntax error parsing SQL identifier ‘[]’.

This is because our table name contains Identifier [ as well as Identifier ]. One of the method was to rename table was to use Double Quotes around the identifier.

sp_rename '"[]"', 'ProjectA';

When we run above query, it will give us success message and rename our table to the new name.

At the end of the blog post, I asked if there is any other way to the same task.

SQL Expert Parth Malhan answered in the comment with alternative solution where he demonstrates that we can use identifier around the name of the table and rename the column as well.

EXEC sys.sp_rename '[[]]]','ProjectA'

Thanks Parth, very cool trick!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name

Just a day ago, my old colleague sent me an email.

“I accidently renamed my tablename as a [], and now I am not able to rename it back with the help of T-SQL to its original name which was ProjectA. Is there any way to fix it?”

Very interesting question. If you want to rename your table name, you can use sp_rename procedure to rename your table. Here is an earlier blog post, I have written on this subject SQL SERVER – How to Rename a Column Name or Table Name. However, if you have [ or ] in the name, you can not straight forward use the SP and rename the table.

First, we will try our usual way to rename the table.

sp_rename '[]', 'ProjectA';

The above query will give us following error:

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107
Syntax error parsing SQL identifier ‘[]’.

This is because our table name contains Identifier [ as well as Identifier ].

If your table name contains identifier, and if you want to rename it, you should run wrap the tablename with double quotes (“) and use the sp_rename command. Here is the example

sp_rename '"[]"', 'ProjectA';

When we run above query, it will give us success message and rename our table to the new name.

Let me know if you follow any other method to rename table when there is identifier in the tablename.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Check Database Level (IsNullConcat) and Session Level Settings (CONCAT_NULL_YIELDS_NULL) using T-SQL

Earlier I wrote a blog post SQL SERVER – A Quick Note on CONCAT_NULL_YIELDS_NULL and in follow up to the blog post, I received few questions. Let me try to answer those questions in following blog post.

Q: How do we know if setting which returns NULL when concated to another NULL value is ON at database level?

A: You can run following script to identify the settings of the database level. In my script I have described AdventureWorks database and is checking if the settings which returns NULL when any other value is concated with NULL or not. If the query returns 1 that means when any value is concated with NULL it will return NULL. If the query returns 0 it means when any value is concated with NULL, it will still return original value and impact of NULL will be ignored.

-- Check Database Level settings
SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'IsNullConcat');

Q: How do we know if setting which returns NULL when concated to another NULL value is ON at session level?

A: You can run following script to identify the settings of the session level. If the query returns 1 that means when any value is concated with NULL it will return NULL. If the query returns 0 it means when any value is concated with NULL, it will still return original value and impact of NULL will be ignored.

-- Check Session Level settings
SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL');

Additionally, you can read comment of  Manigandan as he has demonstrated that we can also get the impact of function ISNULL with using another function COALESCE.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Refresh SSMS Intellisense Cache to Update Schema Changes

Have you ever faced situation where you have just created or modified object but SSMS still shows the error. I quite often face this situation where I come across situation where my SSMS Intellisense Cache is not refreshed or updated. This is indeed very frustrating when you are presenting something on stage as the red underline means an error in graved in many people’s minds and it is hard for them to believe when the code with underline runs successfully.

Here is image of the recent situation. Where I had just dropped index but SSMS Intellisense was still showing that the index exists.

When I face this kind of situation, without wasting time I immediately do following : CTRL + SHIFT + R . This will clear the cache of the Intellisense and it removes the underline from the word. You can also refresh the Intellisense cache by using Edit -> Intellisense -> Refresh Local Cache.

Here is another question back to you – do you use SSMS Intellisense or any other tool?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – How to Set Variable and Use Variable in SQLCMD Mode

Here is the question which I received the other day on SQLAuthority Facebook page. Social media is a wonderful thing and I love the active conversation between blog readers and myself – actually I think social media adds lots of human factor to any conversation. Here is the question -

“I am using sqlcmd in SSMS – I am not sure how to declare variable and pass it, for example I have a database and it has table, how can I make the table variable dynamic and pass different value everytime?”

Fantastic question, and here is its very simple answer. First of all, enable sqlcmd mode in SQL Server Management Studio as described in following image.

Now in query editor type following SQL.

:SETVAR DatabaseName “AdventureWorks2012″
:SETVAR SchemaName “Person”
:SETVAR TableName “EmailAddress
USE $(DatabaseName);
SELECT *
FROM $(SchemaName).$(TableName);

Note that I have set the value of the database, schema and table as a sqlcmd variable and I am executing the query using the same parameters.

Well, that was it, sqlcmd is a very simple language to master and it also aids in doing various tasks easily.

If you have any other sqlcmd tips, please leave a comment and I will publish it with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Another lesser known feature of SQL Server Management Studio 2012 – Guest Post by Balmukund Lakhani

This is a fantastic blog post from my dear friend Balmukund ( blog | twitter | facebook ). He had presented a fantastic session in our last UG and there were lots of requests from attendees that he blogs about it. Well, here is the blog post about the same very popular UG session. Let us read the entire blog post in the voice of the Balmukund himself.


In one of my previous guest blog on SQL Authority, I wrote about “Additional Connection Parameter” tab of login screen in SQL Server Management Studio (a.k.a. SSMS). On the similar lines, this blog is going to show little less known new feature of login main screen (“Connect to Server”) of SSMS 2012.

You might have seen below screen countless times and you might wonder what is there is blog about in this simple screen. Well, continue reading and you would get the answer.

Many times, DBA have to login to production server from non-regular machine, may be a developer’s workstation. Once you login to SQL, do your work and close the management studio. Do you know that your server name is saved in management studio? Of course, very useful feature because you may not like to type server name/IP address every time. Whatever servers you have connected, it would be stored by management studio. But sometime, it’s annoying!

What you would do if you want SQL Server Management Studio to forget “all” the servers listed in drop down of Server name? To do that, you need to know how and where it’s stored. You can use one of my favorite tool from sysinternals called Process Monitor (also known as ProcMon) and easily figure out that this is stored in a file under your windows user profile.

Below is the file in SQL 2008 R2 Management Studio.

%appdata%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

For SQL Server 2012, here is what we can see in ProcMon

So, the path is

%appdata%\Microsoft\Microsoft SQL Server\110\Tools\Shell\SqlStudio.bin

So far, you might wonder, where is the new feature? I have been asked by many users to delete entries from SSMS “Connect to Server” server name list. Well, unofficially, you can delete the file directly which we found via ProcMon. Note that delete file to get rid of server list is not officially supported by Microsoft.

Better way to achieve this is provided in SSMS 2012. To delete the servers from the list, highlight the name we want to delete (via keyboard or mouse) and then press delete key via keyboard. We can’t be multi-select and has to be done one by one. We can delete as many entries we want. I have delete few from first screenshot taken and here is the modified version.

This is not available in SQL 2008 R2 and its previous version. This came from feedback given to SQL Server Product group.

Hope you have learned something new today!

Reference: Pinal Dave (http://blog.sqlauthority.com)