New function:Quotename

QuoteName:

Today, we look at the new function in Sql server-2014 which is Quotename.

If you want to append any string with special characters like [,{,'...

then we will use the Quotename function. Below are the few examples.


Example 1:

Append '{' to 'abc'

select QUOTENAME ('abc','{')

Example 2:

Append '[' to 'abc'

 

Finding out Table in each Database of a Server

Below query is used to find out a particular table in each Database of a server.

It will loop through each database and checks for the particular table.

EXEC sp_Msforeachdb "use [?];select '[?]' as DatabaseName, * from sys.tables where name='TableName' "

Query to findout SQL Agent Jobs which uses SSIS packages

Following Query is used to get the list of SQL Agent Jobs which call SSIS packages from
them

USE [msdb]

GO

SELECT

      Job.name AS JobName,

      JStep.step_id,

      JStep.step_name AS StepName,

      JStep.command,

      Job.enabled

FROM   dbo.sysjobs Job

JOIN   dbo.sysjobsteps JStep

      ON  JStep.job_id = Job.job_id

WHERE  JStep.subsystem='SSIS'

      ORDER BY Job.name,step_id