Effortless Tricks for SSMS

Today we learn about Tricks to Magically do things in SQL Server Mgmt. Studio or SSMS in short, without waving a wand. This article will bring you SQL tricks that many of you might not have thought were possible.

From early days onwards, programming language designers had this desire to design languages in which you tell the machine WHAT you want as a result, not HOW to obtain it. For instance, in SQL, you tell the machine that you want to “connect” (JOIN) the user table and the address table and find the users that live in Switzerland. You don’t care HOW the database will retrieve this information (e.g. should the users table be loaded first, or the address table? Should the two tables be joined in a nested loop or with a hashmap? Should all data be loaded in memory first and then filtered for Swiss users, or should we only load Swiss addresses in the first place? Etc.) As with every abstraction, you will still need to know the basics of what’s going on behind the scenes in a database to help the database make the right decisions when you query it. For instance, it makes sense to:

• Establish a formal foreign key relationship between the tables (this tells the database that every address is guaranteed to have a corresponding user)
• Add an index on the search field: The country (this tells the database that specific countries can be found in O(log N) instead of O(N))

But once your database and your application matures, you will have put all the important meta data in place and you can focus on your business logic only. The following tricks show amazing functionality written in only a few lines of declarative SQL, producing simple and also complex output.

Opening SSMS is as simple:

This is generally for show casing your interviewer or colleagues that you are Master in SSMS. Simply click your icon in your Taskbar or ⊞ Win + R  will help you open Run command  and simply type SSMS HAHA as simple BTW its a bonus trick.

Using SQL Queries for your DB

If you are unaware of how much Tables, Procedures, Columns where we use some preferred keyword or Schemas you have in your whole database simply type these Queries:

SELECT * FROM sys.tables WHERE NAME like 'product%';

for example you want all tables in your database where keyword you want to search for is Product 

you simply write this query and will get the your result. similarly if you want to show all procedures in your database simply write query

SELECT * FROM sys.procedures WHERE NAME like 'product%';

this query will return your whole procedures named as mentioned. And what if you find all the Phone Numbers in your data base just simply write this Query which will help you alot.

SELECT object_name(object_id), * FROM sys.columns where NAME like 'phone%'; 

and in case you want to return all schemas named Product simply write 

SELECT * FROM sys.schemas WHERE NAME like 'product%';

and these query solve all your Desired problems in no time.

Note

Doing this steps is simple like Reciting A-Z but at first Run the Database where you want to find all of your stuff. 

HOW TO RECOVER DELETED DATA FROM SQL SERVER TABLE?

There are many reasons for the data deletion problem. Here, we are going to discuss a 100% working and tested solution that lets you retrieve deleted records in SQL Server. We will discuss manual approaches as well as an automated solution.

Manual Approach:

Deleted rows can be restored if the time of deletion is known. This can be done by using the Log Sequence Numbers (LSNs). LSN is a unique identifier for each record in the SQL Server transaction log.

To retrieve deleted rows from SQL Server using LSN, there are few prerequisites that are to be fulfilled. At the time of deletion, you must have a Full Recovery Model or a Logged Recovery Model.

Follow these steps to recover deleted records in SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and other versions:

Step 1. Check the number of rows in the table from which the data was accidentally deleted.

SELECT * FROM Table_name

Step 2. Take a transaction log backup. 

USE DatabaseName

GO

BACKUP LOG [DatabaseName]

TO DISK = N’D:\DatabaseName\RDDTrLog.trn’

WITH NOFORMAT, NOINIT,

NAME = N’DatabaseName-Transaction Log Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Step 3. Get the Transaction ID of deleted records.

USE DatabaseName

GO

Select [Current LSN] LSN], [Transaction ID], Operation, Context, AllocUnitName

FROM

fn_dblog(NULL, NULL)

WHERE Operation = ‘LOP_DELETE_ROWS’

Step 4. Now find the time when the rows (records) were deleted. The transaction ID will help you to find this information.

USE DatabaseName

GO

SELECT

[Current LSN], Operation, [Transaction_ID], [Begin Time], [Transaction_Name], [Transaction SID]

FROM

fn_dblog(NULL, NULL)

WHERE

[Transaction ID] = ‘000:000001f3′

AND

[Operation] = ‘LOP_BEGIN_XACT’

Step 5. Now start the restore process to recover deleted data from SQL Server Table, Row and Column.

Recover Deleted D USE DatabaseName

GO

RESTORE DATABASE DatabaseName_COPY FROM

DISK = ‘D:\DatabaseName\RDDFull.bak’

WITH

MOVE ‘DatabaseName’ TO ‘D:\RecoverDB\DatabaseName.mdf’,

MOVE ‘DatabaseName_log’ TO ‘D:\RecoverDB\DatabaseName_log.ldf’,

REPLACE, NORECOVERY;

GO

Step 6. In the end, check the table whether deleted rows are recovered or not.

USE DatabaseName_Copy GO Select * from Table_name.

Automated Solution:

The above mentioned manual method only works when you have the most updated backup of your database. If you don’t have updated backup to recover deleted records in SQL Server, then you need to use SysTools SQL Recovery software. With this tool, you can retrieve deleted rows from SQL Server table without any data modification. This software allows the user to recover corrupt data from MDF and NDF file with all objects like tables, views, triggers, functions, etc. It also display a preview of deleted records in red colour after recovery. Using this program, one can recover deleted table data in SQL Server and resolve all SQL errors like 5172, 5171, 823, 8946, etc.

To know more about this Topic Refer to this Article.

Store Procedures Keywords:

if  you are using SP alot then you definitely should know this. Go to

Tools -> Options -> Keyboard -> Query Shortcuts

More Keyboard Shortcuts

CTRL +

N = New Query Editor

E = to Run all or selected batch of Query

S = Save

R = Remove all output windows

D = Results in grid

T = Result in Text(execution time mesaages/ any error)

Shift + F = Result in file

U = to Select database name from Dropdown

O = Open

K + C = commenting any line

K + U = Uncomment any line.

Identify who deleted database records in SQL Server – using SSMS

Launch SSMS and Connect to SQL Instance

Right click in the instance -- > Reports --> Standard Reports -->Schema Changes Report

You will get pop up of ‘Schema Changes History’ which will show you the name of the user who deleted records in SQL Server.

Conclusion

so there we are on an end we are not Godly Particles to do things in a swing we have to apply some effort & to make your effort more easier to parse we have these tricks will give some more tips & tricks in future till then BBye...

and yeah dont forget to tell what you want in next Blog, I'll deep dive and google it for you and do comment which trick is your favorite one.

𝕿𝖍𝖆𝖓𝖐𝖘 & 𝕽𝖊𝖌𝖆𝖗𝖉𝖘...

Share on Facebook Share on Twitter Share on Google Plus

About Freaky Analyst

A Passionate Data analyst working with large amounts of data and to turn this data into information, information into insight and insight into valuable decisions. I also have a keen interest in the field of data analysis, data visualization and am fascinated by the power to compress complex datasets into approachable and appealing graphics.