Transact-SQL Syntax Supported by IntelliSense

This topic describes the Transact-SQL statements and syntax elements that are supported by Transact-SQL IntelliSense.

Statements Supported by IntelliSense

Transact-SQL IntelliSense supports only the most commonly used Transact-SQL statements. Some general conditions might prevent IntelliSense from functioning. For more information, see Troubleshooting Transact-SQL IntelliSense.

Note

IntelliSense is not available for encrypted database objects, such as encrypted stored procedures or user-defined functions. The Parameter Info and Quick Info features are not available for the parameters of extended stored procedures and CLR Integration user-defined types.

SELECT Statement

The Transact-SQL editor provides IntelliSense support for the following syntax elements in the SELECT statement:

SELECT	WHERE
FROM	ORDER BY
HAVING	UNION
FOR	GROUP BY
TOP	OPTION (hint)

Additional Transact-SQL Statements That Are Supported

The Transact-SQL editor also provides IntelliSense support for Transact-SQL statements that are shown in the following table.

Transact-SQL statement	Syntax supported
INSERT	All syntax, except the execute_statement clause.
UPDATE	All syntax.
DELETE	All syntax.
DECLARE @local_variable	All syntax.
SET @local_variable	All syntax.
EXECUTE	Execution of user-defined stored procedures, system stored procedures, user-defined functions, and system functions.
CREATE TABLE	All syntax.
CREATE VIEW	All syntax.
CREATE PROCEDURE	All syntax, with the following exceptions: There is no IntelliSense support for the EXTERNAL NAME clause. In the AS clause, IntelliSense supports only the statements and syntax that are listed in this topic.
ALTER PROCEDURE	All syntax, with the following exceptions: There is no IntelliSense support for the EXTERNAL NAME clause. In the AS clause, IntelliSense supports only the statements and syntax that are listed in this topic.
USE	All syntax.

IntelliSense in Supported Statements

Transact-SQL IntelliSense in the Transact-SQL editor supports the following syntax elements when they are used in one of the supported Transact-SQL statements:

• All join types, including APPLY

• PIVOT and UNPIVOT

• References to the following database objects:

  • Databases and schemas

  • Tables, views, table-valued functions, and table expressions

  • Columns

  • Procedures and procedure parameters

  • Scalar functions and scalar expressions

  • Local variables

  • Common table expressions (CTE)

• Database objects that are referenced only in CREATE or ALTER statements in the script or batch, but which do not exist in the database because the script or batch has not yet been run. These objects are as follows:

  • Tables and procedures that have been specified in a CREATE TABLE or CREATE PROCEDURE statement in the script or batch.

  • Changes to tables and procedures that have been specified in an ALTER TABLE or ALTER PROCEDURE statement in the script or batch.

  Note

  IntelliSense is not available for the columns of a CREATE VIEW statement until the CREATE VIEW statement has been executed.

IntelliSense is not provided for the previously listed elements when they are used in other Transact-SQL statements. For example, there is IntelliSense support for column names that are used in a SELECT statement, but not for columns that are used in the CREATE FUNCTION statement.

Examples

Within a Transact-SQL script or batch, IntelliSense in the Transact-SQL editor supports only the statements and syntax that are listed in this topic. The following Transact-SQL code examples show what statements and syntax elements IntelliSense supports. For example, in the following batch, IntelliSense is available for the SELECT statement when it is coded by itself, but not when the SELECT is contained in a CREATE FUNCTION statement.

USE AdventureWorks;
GO
SELECT Name
FROM Production.Product
WHERE Name LIKE N'Road-250%' and Color = N'Red';
GO
CREATE FUNCTION Production.ufn_Red250 ()
RETURNS TABLE
AS
RETURN 
(
    SELECT Name
    FROM AdventureWorks.Production.Product
    WHERE Name LIKE N'Road-250%'
      AND Color = N'Red'
);GO

This functionality also applies to the sets of Transact-SQL statements in the AS clause of a CREATE PROCEDURE or ALTER PROCEDURE statement.

Within a Transact-SQL script or batch, IntelliSense supports objects that have been specified in a CREATE or ALTER statement; however, these objects do not exist in the database because the statements have not been executed. For example, you might enter the following code in the Transact-SQL editor:

USE MyTestDB;
GO
CREATE TABLE MyTable
    (PrimaryKeyCol   INT PRIMARY KEY,
    FirstNameCol      NVARCHAR(50),
   LastNameCol       NVARCHAR(50));
GO
SELECT 

After you type SELECT, IntelliSense lists PrimaryKeyCol, FirstNameCol, and LastNameCol as possible elements in the select list, even if the script has not been executed and MyTable does not yet exist in MyTestDB.

See Also

Concepts

Transact-SQL IntelliSense Overview

Using Transact-SQL Delimiter Matching

Using Transact-SQL Code Snippets

Troubleshooting Transact-SQL IntelliSense