Expand statements

dbForge Studio for SQL Server provides several statement expansion features that simplify SQL writing and reduce the risk of errors.

Expand a wildcard

If the asterisk (*) can be replaced with a list of available columns from tables, views, or other objects specified in the FROM clause, a hint appears when you place the pointer after the asterisk.

Expand the following script:

SELECT * FROM Person.Address a

To replace the asterisk with the list of columns, press the Tab key. In the same document, the following script appears:

SELECT a.AddressID
      ,a.AddressLine1
      ,a.AddressLine2
      ,a.City
      ,a.StateProvinceID
      ,a.PostalCode
      ,a.SpatialLocation
      ,a.rowguid
      ,a.ModifiedDate FROM Person.Address a

Expand a stored procedure

You can replace a call to a stored procedure with the stored procedure body, simplifying the debugging process.

Convert the following script:

EXEC uspGetEmployeeManagers
@BusinessEntityID = 0

Right-click the EXEC statement and select Refactoring > Convert EXEC to Script. In the same document, the following script appears:

DECLARE @BusinessEntityID INT = 0;
BEGIN
  SET NOCOUNT ON;

  -- Use recursive query to list out all Employees required for a particular Manager
  WITH [EMP_cte] ([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [JobTitle], [RecursionLevel]) -- CTE name and columns
  AS
  (SELECT
      e.[BusinessEntityID]
     ,e.[OrganizationNode]
     ,p.[FirstName]
     ,p.[LastName]
     ,e.[JobTitle]
     ,0 -- Get the initial Employee
    FROM [HumanResources].[Employee] e
    INNER JOIN [Person].[Person] AS p
      ON p.[BusinessEntityID] = e.[BusinessEntityID]
    WHERE e.[BusinessEntityID] = @BusinessEntityID
    UNION ALL
    SELECT
      e.[BusinessEntityID]
     ,e.[OrganizationNode]
     ,p.[FirstName]
     ,p.[LastName]
     ,e.[JobTitle]
     ,[RecursionLevel] + 1 -- Join recursive member to anchor
    FROM [HumanResources].[Employee] e
    INNER JOIN [EMP_cte]
      ON e.[OrganizationNode] = [EMP_cte].[OrganizationNode].GetAncestor(1)
    INNER JOIN [Person].[Person] p
      ON p.[BusinessEntityID] = e.[BusinessEntityID])
  -- Join back to Employee to return the manager name 
  SELECT
    [EMP_cte].[RecursionLevel]
   ,[EMP_cte].[BusinessEntityID]
   ,[EMP_cte].[FirstName]
   ,[EMP_cte].[LastName]
   ,[EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
   ,p.[FirstName] AS 'ManagerFirstName'
   ,p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
  FROM [EMP_cte]
  INNER JOIN [HumanResources].[Employee] e
    ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
  INNER JOIN [Person].[Person] p
    ON p.[BusinessEntityID] = e.[BusinessEntityID]
  ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
  OPTION (MAXRECURSION 25)
END;

Expand an INSERT, ALTER, EXEC, or UPDATE statement

You can type the name of a table, view, procedure, function, or trigger in an ALTER, INSERT, EXEC, or UPDATE statement.

Convert the following script:

UPDATE Person.Address

Press Tab to expand it into a full SQL statement template. In the same document, the following script appears:

UPDATE Person.Address 
SET AddressLine1 = N''
   ,AddressLine2 = N''
   ,City = N''
   ,StateProvinceID = 0
   ,PostalCode = N''
   ,SpatialLocation = NULL
   ,rowguid = DEFAULT
   ,ModifiedDate = DEFAULT
WHERE AddressID = 0;

Note

The Studio doesn’t expand ALTER statements for encrypted triggers, views, procedures, and functions.