Scripting all Agent Jobs By Using SQL Server Management Studio

I recently came across a way to script out all agent jobs in one go within SQL Server Management Studio (SSMS). Previously I used to right click on each job and select ‘Script Job as’ to script up each one individually, but this can be very laborious
if there are a lot of jobs to do.

To script all jobs, just open the ‘Object Explorer Details’ from the View menu in SSMS, or press the F7 key. Click on any job in the Object Explorer window and a list of all the agent jobs appears in the ‘Object Explorer Details’ window.

Select all the jobs you want to script (press the Ctrl button while clicking to select individual jobs) and then right click and select the scripting option you want. This will then create all the selected jobs as a single query. You can script to a query window, file etc.

Find out the backup locations/paths for multiple SQL Instances

If you are trying to consolidate backup locations, we first need to analyse where the databases are being backed up to. Below query will give you the location of where the databases were backed up to. Run this in Central Management server and you can get a report of all the SQL Instances in one shot

SET NOCOUNT ON
SELECT CONVERT(CHAR(30), SERVERPROPERTY('Servername')) AS instance_name,
CONVERT(CHAR(30),bs.database_name) AS database_name,
CONVERT(CHAR(125),bf.physical_device_name) backup_path
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bf
ON bs.media_set_id = bf.media_set_id
JOIN master.dbo.sysdatabases db
ON bs.database_name = db.name
WHERE bs.backup_finish_date = (
      SELECT MAX(backup_finish_date)
      FROM msdb.dbo.backupset
      WHERE database_name = bs.database_name
            AND type = 'D'
      )
      AND (DATABASEPROPERTYEX(bs.database_name,'Status') = 'ONLINE'
      AND DATABASEPROPERTYEX(bs.database_name,'IsInStandby') = 0)

Script to generate database Role membership and also to generate commands to add the users to database roles

During the migration of a database from one SQL Instance to another, we might have to re-create the permissions on the target SQL Instance. This is not required in all the cases, but only in some cases where the user permissions are not synced for some reason. So an easy way to script them out before performing the database refresh is to use the below script to capture the existing setup of database role membership:

-- List Database Roles and Members with Server Login
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      ,MEM.type_desc AS MemberType
      ,MEM.default_schema_name AS DefaultSchema
      ,SP.name AS ServerLogin
FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY RoleName
        ,MemberName;

Now after this is captured, you still have to manually run the below commands for each of the users captured:

sp_addrolemember 'Rolename','UserName'

It takes a lot of time to generate a script manually if there are more than 5 or 10 users. Below is a script to generate those commands as well by leveraging on the above script:

SET QUOTED_IDENTIFIER OFF
IF EXISTS (SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME="##USER_PERMISSIONS")
DROP TABLE ##USER_PERMISSIONS
CREATE TABLE ##USER_PERMISSIONS
(ROLENAME VARCHAR (100),
MEMBERNAME VARCHAR(100))
INSERT INTO ##USER_PERMISSIONS
SELECT ROL.name AS RoleName
      ,MEM.name AS MemberName
      FROM sys.database_role_members AS DRM
     INNER JOIN sys.database_principals AS ROL
         ON DRM.role_principal_id = ROL.principal_id
     INNER JOIN sys.database_principals AS MEM
         ON DRM.member_principal_id = MEM.principal_id
     INNER JOIN sys.server_principals AS SP
         ON MEM.[sid] = SP.[sid]
ORDER BY MemberName, Rolename;
SELECT ("sp_addrolemember '"+ ROLENAME +"','"+MEMBERNAME+"'") AS CMD FROM ##user_permissions
go

How To Find How Many Instances Installed In The System By Using T-SQL

Create Table #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

Insert into #SQLInstances
EXECUTE xp_regread
@rootkey ='HKEY_LOCAL_MACHINE',
@key ='SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name ='InstalledInstances'


select instancename,Data from #SQLInstances

Important New DMV's Features

There are couple of new DMV’s. But I would like to share with you all.Some Useful new DMV’s in SQL Server 2012 and SQL Server 2008 R2 SP1

sys.dm_os_windows_info: This DMV will get you OS related information of the Server.

sys.dm_server_registry: This DMV will get you information on registry keys which are used by the server.

sys.dm_server_memory_dumps : This DMV will get you information on dumps which are generated by the server.

sys.dm_server_services: This DMV will get you information on services and startup’s

Splitting comma separated column’s values in rows in SQL Server And Vice Versa

I have seen many Developer/DBA uses user defined function or stored procedure to split a comma separated ( or any delimiter to separate values like |,;-.& or any character/special character) column into rows using various coding logic like while/for loop or using cursor etc.

Let see an example, below is a sample data,

     dbaraja.blogspot.in                  

                  

And my requirement is to split the every SkillSet column values in a single row according EMPID and EMPNAME like

          dbaraja.blogspot.in                                                                   

                                                                           

To get above desired result without using any Function, Stored Procedure or any loop, simple flat SQL select statement will be like this,

SELECT EmpID,EmpName,Split.Data.value('.', 'VARCHAR(100)') AS Skill

FROM (Select EmpID,EmpName,CAST('<M>' + REPLACE(SkillSet,',', '</M><M>')+ '</M>' AS XML) AS Skill FROM Rajendra_Test) AS Data 

CROSS APPLY Skill.nodes ('/M') AS Split(Data)

                           ------------------------------------------------
The below Query is used to retrieve the opposite of above process.

;WITH CTE AS
(
SELECT DISTINCT EmpId
FROM Employee
)
SELECT EmpId, STUFF((SELECT ', ' + CAST(E2.Skill AS NVARCHAR(MAX))
     FROM Employee E2 WHERE E1.EmpId = E2.EmpId
     FOR XML PATH('')),1,1,'') skills
  into Employee1
FROM CTE E1

Select * from Employee1 --Getting Result--

              

Script to list currently executing queries in SQL Server

 
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
 
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
 
 CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
 
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
 
 CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
 
 CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
 
 CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
 
 FROM sys.dm_exec_sql_text(sql_handle)))
 
 FROM sys.dm_exec_requests r

WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')