In this article I will be listing the tips and tricks to work like
a Pro.
So let's get started –
Important keyboard shortcuts for Sql Server Management
Studio.
CTRL+N – Opens new query window
CTRL+SHIFT+N – Open pop window to
create new project
CTRL+SHIFT+TAB – Cycle through the
previous MDI child windows
END – Move the cursor to the end of
the line
HOME – Move the cursor to the
beginning of the line
CTRL+HOME – Move the cursor to the
beginning of the document
CTRL+END – Move the cursor to the
end of the document
PAGE UP – Move the cursor up one
screen
PAGE DOWN - Move the cursor down
one screen
CTRL+ RIGHT ARROW – Move cursor to
one word towards right
CTRL+ LEFT ARROW – Move cursor to
one word towards left
CTRL+PAGE UP – Moves the cursor to
the top of the document
CTRL+PAGE DOWN – Moves the cursor
to the bottom of the document
CTRL+UP ARROW - Scroll text up one
line
CTRL+DOWN ARROW - Scroll text down
one line
CTRL+Z - Reverse the last editing
action
CTRL+Y - Restore the previously
undone edit
F5 - Run the selected portion of
the query editor or the entire query editor if nothing is selected
If you want to see the whole list , please
visit the below link from Microsoft website.
---------------------------------------------------------------------
Important System tables and queries
Get the list of databases on the server -
select * from Sys.databases
Get the list of objects (tables, constraints,
Views, Triggers etc.) in your database and basic details -
Select Obj.name,Obj.type,Obj.type_desc,Obj.create_date,Obj.modify_date
from Sys.objects Obj
Get the list of all columns in a table or list of
all tables and columns –
Select Obj.name,Obj.type_desc,Col.name from Sys.objects Obj
Inner Join Sys.columns Col on Col.object_id=Obj.object_id
Where Obj.name='Employee'
Order by Obj.name,Col.name
Select Obj.name,Obj.type_desc,Col.name from Sys.objects Obj
Inner Join Sys.columns Col on Col.object_id=Obj.object_id
Order by Obj.name,Col.name
Get the list of table and
indexes on them -
Select Obj.name,Obj.type_desc,Ind.name from Sys.objects Obj
Inner Join Sys.indexes Ind on Ind.object_id=Obj.object_id
Where Obj.name='Employee'
Order by Obj.name,Ind.name
Select Obj.name,Obj.type_desc,Ind.name from Sys.objects Obj
Inner Join Sys.indexes Ind on Ind.object_id=Obj.object_id
Order by Obj.name,Ind.name
Get the list of user defined views -
select Vw.name,Vw.schema_id,Vw.type_desc,Vw.create_date,Vw.modify_date
from Sys.all_views Vw where Vw.is_ms_shipped=0
----------------------------------------------------------------------------
sp_helptext - this could lot of
your time, instead of browsing through the list of stored procedure, you can
use this. Here is the syntax
sp_helptext ‘uspGetBillOfMaterials
sp_helpindex – this provide the details about the index on the
table like index name, description and key.
sp_helpindex '[HumanResources].[Employee]'
index_name
|
index_description
|
index_keys
|
AK_Employee_LoginID
|
nonclustered, unique
located on PRIMARY
|
LoginID
|
AK_Employee_NationalIDNumber
|
nonclustered, unique
located on PRIMARY
|
NationalIDNumber
|
AK_Employee_rowguid
|
nonclustered, unique
located on PRIMARY
|
rowguid
|
IX_Employee_OrganizationLevel_OrganizationNode
|
nonclustered located on
PRIMARY
|
OrganizationLevel,
OrganizationNode
|
IX_Employee_OrganizationNode
|
nonclustered located on
PRIMARY
|
OrganizationNode
|
PK_Employee_BusinessEntityID
|
clustered, unique,
primary key located on PRIMARY
|
BusinessEntityID
|
sp_helpstats – this one provides statistics details
about columns and indexes on the table.
sp_helpstats '[Sales].[SalesOrderDetail]'
statistics_name
|
statistics_keys
|
_WA_Sys_00000006_44CA3770
|
SpecialOfferID
|
sp_helptrigger - It provides the information about DML triggers on the given table.
sp_helptrigger
'[Sales].[SalesOrderDetail]'
trigger_name
|
trigger_owner
|
isupdate
|
isdelete
|
isinsert
|
isafter
|
isinsteadof
|
trigger_schema
|
iduSalesOrderDetail
|
dbo
|
1
|
1
|
1
|
1
|
0
|
Sales
|
