Ctrl+R doesn't work in SQL Server 2012 SSMS

Problem: Ctrl+R doesn't work in SQL Server 2012 SSMS!!!

When I started work with SSMS of SQL Server 32012, and tried to hide/display the result panel, using Ctrl+R – it doesn't work for me, and I’v got this error in the bottom of the SSMS:

(Ctrl+R) was pressed. Waiting for second key of chord.

Solution:

Open from the menu: Toolsàoptions, choose KeyboardàKeyboard.

There, choose from the list: “window.ShowResultPane” event, and assign the Ctrl+R to “Use new shortcut in: SQL Query Editor”.

(Don’t forget to press the “Assign” button…)

Script fpr Role creation including permissions

-- set here the role name:

declare @RoleName varchar(50) = 'ROLE_NAME'

declare @RoleScript varchar(max)

SELECT @RoleScript = 'CREATE ROLE ' + @RoleName + char(13)

SELECT @RoleScript = @RoleScript + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS

FROM   sys.database_permissions prm

JOIN      sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id

WHERE  rol.name = @RoleName

PRINT @RoleScript

Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Error message:

Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But if I don’t want this process to be the deadlock victim?

Solution:

SET DEADLOCK_PRIORITY HIGH

see here:

http://copypastenet.blogspot.co.il/2014/01/set-deadlockpriority.html

Drop few columns in one statement

ALTER TABLE dbo.MyTable DROP COLUMN ColumnA, ColumnB, ColumnC

Pivot On Multiple Columns

------------------------------------------------------------------------------

-- temp table for the example

CREATE TABLE tmpResults

( CustomerType nvarchar(50),

SomeTypeLetter nvarchar(50),

NumOfCustomers int

)

INSERT INTO tmpResults (CustomerType, SomeTypeLetter, NumOfCustomers)

VALUES ('CustomerType1', 'SomeTypeA', 33),

('CustomerType1', 'SomeTypeB', 12),

('CustomerType1', 'SomeTypeC', 61),

('CustomerType2', 'SomeTypeA', 11),

('CustomerType2', 'SomeTypeB', 25),

('CustomerType2', 'SomeTypeC', 21),

('CustomerType3', 'SomeTypeA', 17),

('CustomerType3', 'SomeTypeB', 38),

('CustomerType3', 'SomeTypeC', 9),

('CustomerType4', 'SomeTypeA', 5),

('CustomerType4', 'SomeTypeB', 91),

('CustomerType4', 'SomeTypeC', 12)

--SELECT * FROM tmpResults

------------------------------------------------------------------------------

--Pivot On Multiple Columns:

SELECT [CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

FROM 

(   SELECT  isnull(p.NumOfCustomers,0) as NumOfCustomers,

(p.CustomerType) + '-' + (p.SomeTypeLetter) as [desc]

FROM    tmpResults p

) [base]

PIVOT

(   SUM(NumOfCustomers)

FOR [desc] IN

(

[CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

)

) [pvt]

DROP TABLE tmpResults

T-SQL that return data of the SQL Server databases

Select @@SERVERNAME -- Server and instance name

Select @@VERSION -- SQL Server Version

Select @@ServiceName -- SQL Server Instance

Select DB_NAME() -- Current Database

SELECT * FROM sys.servers ORDER BY name --Linked Servers

--Last Databases Backup:

SELECT  @@Servername AS ServerName ,

        d.Name AS DBName ,

        MAX(b.backup_finish_date) AS LastBackupCompleted

FROM    sys.databases d

LEFT OUTER JOIN msdb..backupset b 

        ON b.database_name = d.name AND b.[type] = 'D'

GROUP BY d.Name

ORDER BY d.Name;

And more - in the article "Exploring Your SQL Server Databases with T-SQL":

https://www.simple-talk.com/sql/database-administration/exploring-your-sql-server-databases-with-t-sql/?utm_source=ssc&utm_medium=publink&utm_content=exploringwithtsql

Partition info

select distinct

object_name(p.object_id),

      p.partition_number,

      pf.name,

      ps.name,

      prv.value,

      p.data_compression_desc,

      fg.name,

      p.rows

from  sys.partitions (nolock) p

inner join sys.indexes (nolock)i on p.object_id = i.object_id and p.index_id = i.index_id

inner join sys.partition_schemes ps (nolock) on ps.data_space_id=i.data_space_id

inner join sys.partition_functions (nolock)pf on pf.function_id=ps.function_id

inner join sys.partition_range_values(nolock) prv on p.partition_number = prv.boundary_id and prv.function_id=pf.function_id

inner join sys.allocation_units au  ON au.container_id = p.hobt_id

inner join sys.filegroups fg  ON fg.data_space_id = au.data_space_id

where pf.name='PF_Name'

and object_name(p.object_id) = 'TableName'

--and partition_number<386

--and cast(prv.value as datetime) > '2014-03-04 00:00:00.000'

order by object_name(p.object_id),p.partition_number-- desc