Tested on server version: SQL Server 2016 Developer Edition
Reference info accurate on: January, 2018
The following script generates a view that returns a comprehensive list of all SQL Server system objects on a server (that the user has some permissions on), along with the DDL used to create them.
System objects include::
- AGGREGATE_FUNCTION
- CLR_SCALAR_FUNCTION
- CLR_STORED_PROCEDURE
- EXTENDED_STORED_PROCEDURE
- INTERNAL_TABLE
- SERVICE_QUEUE
- SQL_INLINE_TABLE_VALUED_FUNCTION
- SQL_SCALAR_FUNCTION
- SQL_STORED_PROCEDURE
- SQL_TABLE_VALUED_FUNCTION
- SQL_TRIGGER
- SYNONYM
- SYSTEM_TABLE
- TYPE_TABLE
- USER_TABLE
- VIEW
Example output:
CREATE VIEW dbo.vwSysObjects
AS
-- msdb - No Schema in Object Name
SELECT
s.[name] as [SysObjSchema],
o.[name] as [SysObjName],
s.[name] + '.' + o.[name] as [SysObjPath],
o.[object_id] as [SysObjId],
o.[type_desc] as [SysObjType],
m.[definition] as [SysObjDefinition]
FROM msdb.sys.all_objects o
LEFT JOIN msdb.sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN msdb.sys.all_sql_modules m ON o.[object_id] = m.[object_id]
WHERE
o.[type_desc] NOT IN ('UNIQUE_CONSTRAINT','DEFAULT_CONSTRAINT','FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT','CHECK_CONSTRAINT')
and o.is_ms_shipped=1
UNION
-- master - No Schema in Object Name
SELECT
s.[name] as [SysObjSchema],
o.[name] as [SysObjName],
s.[name] + '.' + o.[name] as [SysObjPath],
o.[object_id] as [SysObjId],
o.[type_desc] as [SysObjType],
m.[definition] as [SysObjDefinition]
FROM [master].sys.all_objects o
LEFT JOIN [master].sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN [master].sys.all_sql_modules m ON o.[object_id] = m.[object_id]
WHERE
o.[type_desc] NOT IN ('UNIQUE_CONSTRAINT','DEFAULT_CONSTRAINT','FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT','CHECK_CONSTRAINT')
and o.is_ms_shipped=1
UNION
-- model - No Schema in Object Name
SELECT
s.[name] as [SysObjSchema],
o.[name] as [SysObjName],
s.[name] + '.' + o.[name] as [SysObjPath],
o.[object_id] as [SysObjId],
o.[type_desc] as [SysObjType],
m.[definition] as [SysObjDefinition]
FROM [model].sys.all_objects o
LEFT JOIN [model].sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN [model].sys.all_sql_modules m ON o.[object_id] = m.[object_id]
WHERE
o.[type_desc] NOT IN ('UNIQUE_CONSTRAINT','DEFAULT_CONSTRAINT','FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT','CHECK_CONSTRAINT')
and o.is_ms_shipped=1
--- Snippet #FDEE6B31572B7ECEF209B15854432A96 (TSQL)
--- The latest version of this code is at:
--- https://www.sevendaysofschema.com/tsql-examples/return-all-system-object-definitions/
Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.
In this Script
sys.all_sql_modules sys.all_objects sys.schemas CREATE VIEW OBJECT_ID SCHEMA_ID SELECT UNION WHERE LEFT FROM AND NOT IN
sys.all_sql_modules (Transact-SQL) 
Returns the union of sys.sql_modules and sys.system_sql_modules.
The view returns a row for each natively compiled, scalar user-defined function. See Scalar User-Defined Functions for In-Memory OLTP.
Permissions: The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. See Metadata Visibility Configuration.
object_id int definition nvarchar(max) uses_ansi_nulls bit uses_quoted_identifier bit is_schema_bound bit uses_database_collation bit is_recompiled bit null_on_null_input bit execute_as_principal_id int
Related Topics: Catalog Views In-Memory OLTP Object Catalog Views sys.sql_modules sys.system_sql_modules
System Catalog Views: dbo.sysdac_instances sys.all_columns sys.all_objects sys.all_parameters sys.all_views sys.change_tracking_databases sys.change_tracking_tables sys.column_store_dictionaries sys.column_store_row_groups sys.column_store_segments sys.column_type_usages sys.data_spaces sys.database_automatic_tuning_options sys.database_credentials sys.database_event_session_actions sys.database_event_session_events sys.database_event_session_fields sys.database_event_session_targets sys.database_event_sessions sys.database_mirroring_witnesses
sys.all_objects (Transact-SQL)
Shows the UNION of all schema-scoped user-defined objects and system objects.
Permissions: The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. See Metadata Visibility Configuration.
name sysname object_id int principal_id int schema_id int parent_object_id int type char(2) type_desc nvarchar(60) create_date datetime modify_date datetime is_ms_shipped bit is_published bit
Related Topics: Catalog Views Object Catalog Views sys.objects sys.system_objects
System Catalog Views: dbo.sysdac_instances sys.all_columns sys.all_parameters sys.all_views sys.change_tracking_databases sys.change_tracking_tables sys.column_store_dictionaries sys.column_store_row_groups sys.column_store_segments sys.column_type_usages sys.data_spaces sys.database_automatic_tuning_options sys.database_credentials sys.database_event_session_actions sys.database_event_session_events sys.database_event_session_fields sys.database_event_session_targets sys.database_event_sessions sys.database_mirroring_witnesses
Contains a row for each database schema.
NOTE: Database schemas are different from XML schemas, which are used to define the content model of XML documents.
Permissions: Requires membership in the public role. See Metadata Visibility Configuration.
name sysname
schema_id int
principal_id int
Related Topics: Catalog Views sys.objects
System Catalog Views: dbo.sysdac_instances sys.all_columns sys.all_parameters sys.all_views sys.change_tracking_databases sys.change_tracking_tables sys.column_store_dictionaries sys.column_store_row_groups sys.column_store_segments sys.column_type_usages sys.data_spaces sys.database_automatic_tuning_options sys.database_credentials sys.database_event_session_actions sys.database_event_session_events sys.database_event_session_fields sys.database_event_session_targets sys.database_event_sessions
Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database. For example, a view can be used for the following purposes:
– To focus, simplify, and customize the perception each user has of the database.
– As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.
– To provide a backward compatible interface to emulate a table whose schema has changed.
Permissions: Requires CREATE VIEW permission in the database and ALTER permission on the schema in which the view is being created.
-- Syntax for SQL Server and Azure SQL Database
CREATE [ OR ALTER ] VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ]
[ ; ]
<view_attribute> ::=
{
[ ENCRYPTION ]
[ SCHEMABINDING ]
[ VIEW_METADATA ]
}
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
CREATE VIEW [ schema_name . ] view_name [ ( column_name [ ,...n ] ) ]
AS <select_statement>
[;]
<select_statement> ::=
[ WITH <common_table_expression> [ ,...n ] ]
SELECT <select_criteria>
/*
Partitioned Views
*/
--Partitioned view as defined on Server1
CREATE VIEW Customers
AS
--Select from local member table.
SELECT *
FROM CompanyData.dbo.Customers_33
UNION ALL
--Select from member table on Server2.
SELECT *
FROM Server2.CompanyData.dbo.Customers_66
UNION ALL
--Select from mmeber table on Server3.
SELECT *
FROM Server3.CompanyData.dbo.Customers_99;
/*
Conditions for Creating Partitioned Views
*/
C1 ::= < simple_interval > [ OR < simple_interval > OR ...]
< simple_interval > :: =
< col > { < | > | \<= | >= | = < value >}
| < col > BETWEEN < value1 > AND < value2 >
| < col > IN ( value_list )
| < col > { > | >= } < value1 > AND
< col > { < | <= } < value2 >
/*
A. Using a simple CREATE VIEW
*/
CREATE VIEW hiredate_view
AS
SELECT p.FirstName, p.LastName, e.BusinessEntityID, e.HireDate
FROM HumanResources.Employee e
JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID ;
GO
/*
B. Using WITH ENCRYPTION
*/
CREATE VIEW Purchasing.PurchaseOrderReject
WITH ENCRYPTION
AS
SELECT PurchaseOrderID, ReceivedQty, RejectedQty,
RejectedQty / ReceivedQty AS RejectRatio, DueDate
FROM Purchasing.PurchaseOrderDetail
WHERE RejectedQty / ReceivedQty > 0
AND DueDate > CONVERT(DATETIME,'20010630',101) ;
GO
/*
C. Using WITH CHECK OPTION
*/
CREATE VIEW dbo.SeattleOnly
AS
SELECT p.LastName, p.FirstName, e.JobTitle, a.City, sp.StateProvinceCode
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress bea
ON bea.BusinessEntityID = e.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = bea.AddressID
INNER JOIN Person.StateProvince sp
ON sp.StateProvinceID = a.StateProvinceID
WHERE a.City = 'Seattle'
WITH CHECK OPTION ;
GO
/*
D. Using built-in functions within a view
*/
CREATE VIEW Sales.SalesPersonPerform
AS
SELECT TOP (100) SalesPersonID, SUM(TotalDue) AS TotalSales
FROM Sales.SalesOrderHeader
WHERE OrderDate > CONVERT(DATETIME,'20001231',101)
GROUP BY SalesPersonID;
GO
/*
E. Using partitioned data
*/
--Create the tables and insert the values.
CREATE TABLE dbo.SUPPLY1 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 1 and 150),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY2 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 151 and 300),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY3 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 301 and 450),
supplier CHAR(50)
);
CREATE TABLE dbo.SUPPLY4 (
supplyID INT PRIMARY KEY CHECK (supplyID BETWEEN 451 and 600),
supplier CHAR(50)
);
GO
INSERT dbo.SUPPLY1 VALUES ('1', 'CaliforniaCorp'), ('5', 'BraziliaLtd')
, ('231', 'FarEast'), ('280', 'NZ')
, ('321', 'EuroGroup'), ('442', 'UKArchip')
, ('475', 'India'), ('521', 'Afrique');
GO
--Create the view that combines all supplier tables.
CREATE VIEW dbo.all_supplier_view
WITH SCHEMABINDING
AS
SELECT supplyID, supplier
FROM dbo.SUPPLY1
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY2
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY3
UNION ALL
SELECT supplyID, supplier
FROM dbo.SUPPLY4;
/*
F. Creating a simple view
*/
CREATE VIEW DimEmployeeBirthDates AS
SELECT FirstName, LastName, BirthDate
FROM DimEmployee;
/*
G. Create a view by joining two tables
*/
CREATE VIEW view1
AS
SELECT fis.CustomerKey, fis.ProductKey, fis.OrderDateKey,
fis.SalesTerritoryKey, dst.SalesTerritoryRegion
FROM FactInternetSales AS fis
LEFT OUTER JOIN DimSalesTerritory AS dst
ON (fis.SalesTerritoryKey=dst.SalesTerritoryKey);
Related Topics: ALTER TABLE ALTER VIEW Create a Stored Procedure DELETE DROP VIEW EVENTDATA INSERT sp_help sp_helptext sp_refreshview sp_rename sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities sys.views UPDATE
CREATE Statements: CREATE AGGREGATE CREATE APPLICATION ROLE CREATE ASSEMBLY CREATE ASYMMETRIC KEY CREATE AVAILABILITY GROUP CREATE BROKER PRIORITY CREATE CERTIFICATE CREATE COLUMN ENCRYPTION KEY CREATE COLUMN MASTER KEY CREATE COLUMNSTORE INDEX CREATE CONTRACT CREATE CREDENTIAL CREATE CRYPTOGRAPHIC PROVIDER CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE CREATE DATABASE AUDIT SPECIFICATION CREATE DATABASE ENCRYPTION KEY CREATE DATABASE SCOPED CREDENTIAL
Returns the database object identification number of a schema-scoped object.
IMPORTANT: Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use `SELECT OBJECT_ID FROM sys.triggers WHERE name = ‘DatabaseTriggerLog“’`.
OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )
/*
A. Returning the object ID for a specified object
*/
USE master;
GO
SELECT OBJECT_ID(N'AdventureWorks2012.Production.WorkOrder') AS 'Object ID';
GO
/*
B. Verifying that an object exists
*/
USE AdventureWorks2012;
GO
IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
DROP TABLE dbo.AWBuildVersion;
GO
/*
C. Using OBJECT_ID to specify the value of a system function parameter
*/
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2012');
SET @object_id = OBJECT_ID(N'AdventureWorks2012.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO
/*
D: Returning the object ID for a specified object
*/
SELECT OBJECT_ID('AdventureWorksPDW2012.dbo.FactFinance') AS 'Object ID';
Related Topics: Metadata Functions OBJECT_DEFINITION OBJECT_NAME sys.dm_db_index_operational_stats sys.objects
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COL_NAME COLUMNPROPERTY DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY
Returns the schema ID associated with a schema name.
SCHEMA_ID ( [ schema_name ] )
/*
A. Returning the default schema ID of a caller
*/
SELECT SCHEMA_ID();
/*
B. Returning the schema ID of a named schema
*/
SELECT SCHEMA_ID('dbo');
Related Topics: Metadata Functions SCHEMA_NAME sys.schemas
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COL_NAME COLUMNPROPERTY DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY
Retrieves one or more rows or columns from the database. The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set. <Rick’s Tip> The SELECT syntax used for Azure SQL Data Warehouse and Parallel Data Warehouse differs from that used for SQL Server and Azure SQL Database.
Permissions: Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.
-- Syntax for SQL Server and Azure SQL Database
<SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
[ WITH <common_table_expression> [ ,...n ] ]
SELECT <select_criteria>
[;]
<select_criteria> ::=
[ TOP ( top_expression ) ]
[ ALL | DISTINCT ]
{ * | column_name | expression } [ ,...n ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE <search_condition> ]
[ GROUP BY <group_by_clause> ]
[ HAVING <search_condition> ]
[ ORDER BY <order_by_expression> ]
[ OPTION ( <query_option> [ ,...n ] ) ]
/* A. Using SELECT to retrieve rows and columns */ SELECT * FROM DimEmployee ORDER BY LastName; /* B. Using SELECT with column headings and calculations */ SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay FROM DimEmployee ORDER BY LastName; /* C. Using DISTINCT with SELECT */ SELECT DISTINCT Title FROM DimEmployee ORDER BY Title; /* D. Using GROUP BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* E. Using GROUP BY with multiple groups */ SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey, PromotionKey ORDER BY OrderDateKey; /* F. Using GROUP BY and WHERE */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales WHERE OrderDateKey > '20020801' GROUP BY OrderDateKey ORDER BY OrderDateKey; /* G. Using GROUP BY with an expression */ SELECT SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY (OrderDateKey * 10); /* H. Using GROUP BY with ORDER BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* I. Using the HAVING clause */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey HAVING OrderDateKey > 20010000 ORDER BY OrderDateKey;
Related Topics: Hints SELECT Examples
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING IS NULL PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WHERE WRITETEXT
Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.
The following are basic rules for combining the result sets of two queries by using UNION:
– The number and the order of the columns must be the same in all queries.
– The data types must be compatible.
{ <query_specification> | ( <query_expression> ) }
UNION [ ALL ]
<query_specification | ( <query_expression> )
[ UNION [ ALL ] <query_specification> | ( <query_expression> )
[ ...n ] ]
/*
A. Using a simple UNION
*/
-- Uses AdventureWorks
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO
-- Here is the simple union.
-- Uses AdventureWorks
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO
/*
B. Using SELECT INTO with UNION
*/
-- Uses AdventureWorks
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO
-- Uses AdventureWorks
SELECT ProductModelID, Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO
SELECT ProductModelID, Name
FROM dbo.ProductResults;
/*
C. Using UNION of two SELECT statements with ORDER BY
*/
-- Uses AdventureWorks
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO
/ INCORRECT /
-- Uses AdventureWorks
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO
/ CORRECT /
-- Uses AdventureWorks
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO
/*
D. Using UNION of three SELECT statements to show the effects of ALL and parentheses
*/
-- Uses AdventureWorks
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeOne
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeTwo
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle
INTO dbo.EmployeeThree
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
-- Union ALL
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName, FirstName ,JobTitle
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName,JobTitle
FROM dbo.EmployeeThree;
GO
SELECT LastName, FirstName,JobTitle
FROM dbo.EmployeeOne
UNION
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeThree;
GO
SELECT LastName, FirstName,JobTitle
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeThree
);
GO
/*
E. Using a simple UNION
*/
-- Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
UNION
SELECT CustomerKey
FROM DimCustomer
ORDER BY CustomerKey;
/*
F. Using UNION of two SELECT statements with ORDER BY
*/
-- Uses AdventureWorks
-- INCORRECT
SELECT CustomerKey
FROM FactInternetSales
ORDER BY CustomerKey
UNION
SELECT CustomerKey
FROM DimCustomer
ORDER BY CustomerKey;
-- CORRECT
USE AdventureWorksPDW2012;
SELECT CustomerKey
FROM FactInternetSales
UNION
SELECT CustomerKey
FROM DimCustomer
ORDER BY CustomerKey;
/*
G. Using UNION of two SELECT statements with WHERE and ORDER BY
*/
-- Uses AdventureWorks
-- INCORRECT
SELECT CustomerKey
FROM FactInternetSales
WHERE CustomerKey >= 11000
ORDER BY CustomerKey
UNION
SELECT CustomerKey
FROM DimCustomer
ORDER BY CustomerKey;
-- CORRECT
USE AdventureWorksPDW2012;
SELECT CustomerKey
FROM FactInternetSales
WHERE CustomerKey >= 11000
UNION
SELECT CustomerKey
FROM DimCustomer
ORDER BY CustomerKey;
/*
H. Using UNION of three SELECT statements to show effects of ALL and parentheses
*/
-- Uses AdventureWorks
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
UNION ALL
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
UNION ALL
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer;
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
UNION
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
UNION
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer;
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
UNION ALL
(
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
UNION
SELECT CustomerKey, FirstName, LastName
FROM DimCustomer
);
Related Topics: SELECT SELECT Examples
T-SQL Language Elements: BEGIN DISTRIBUTED TRANSACTION BEGIN TRANSACTION BEGIN…END BREAK CASE CLOSE COALESCE COMMIT TRANSACTION COMMIT WORK CONTINUE CREATE DIAGNOSTICS SESSION DEALLOCATE DECLARE CURSOR EXCEPT and INTERSECT EXECUTE FETCH GO GOTO IF…ELSE NULL and UNKNOWN
Specifies the search condition for the rows returned by the query.
[ WHERE <search_condition> ]
/*
A. Finding a row by using a simple equality
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName = 'Smith' ;
/*
B. Finding rows that contain a value as part of a string
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName LIKE ('%Smi%');
/*
C. Finding rows by using a comparison operator
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500;
/*
D. Finding rows that meet any of three conditions
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey = 1 OR EmployeeKey = 8 OR EmployeeKey = 12;
/*
E. Finding rows that must meet several conditions
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey <= 500 AND LastName LIKE '%Smi%' AND FirstName LIKE '%A%';
/*
F. Finding rows that are in a list of values
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE LastName IN ('Smith', 'Godfrey', 'Johnson');
/*
G. Finding rows that have a value between two values
*/
-- Uses AdventureWorks
SELECT EmployeeKey, LastName
FROM DimEmployee
WHERE EmployeeKey Between 100 AND 200;
Related Topics: DELETE MERGE Predicates Search Condition SELECT UPDATE
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING IS NULL PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WRITETEXT
Returns the left part of a character string with the specified number of characters.
LEFT ( character_expression , integer_expression )
/*
A. Using LEFT with a column
*/
SELECT LEFT(Name, 5)
FROM Production.Product
ORDER BY ProductID;
GO
/*
B. Using LEFT with a character string
*/
SELECT LEFT('abcdefg',2);
GO
/*
C. Using LEFT with a column
*/
-- Uses AdventureWorks
SELECT LEFT(EnglishProductName, 5)
FROM dbo.DimProduct
ORDER BY ProductKey;
/*
D. Using LEFT with a character string
*/
-- Uses AdventureWorks
SELECT LEFT('abcdefg',2) FROM dbo.DimProduct;
Related Topics: CAST CAST and CONVERT Data Types LTRIM RIGHT RTRIM String Functions STRING_SPLIT SUBSTRING TRIM
String Functions: ASCII CHAR CHARINDEX CONCAT CONCAT_WS DIFFERENCE FORMAT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements in SQL Server 2017. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
Permissions: Requires the permissions for the DELETE, SELECT, or UPDATE statement.
-- Syntax for SQL Server and Azure SQL Database
[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
[ <tablesample_clause> ]
[ WITH ( < table_hint > [ [ , ]...n ] ) ]
| rowset_function [ [ AS ] table_alias ]
[ ( bulk_column_alias [ ,...n ] ) ]
| user_defined_function [ [ AS ] table_alias ]
| OPENXML <openxml_clause>
| derived_table [ [ AS ] table_alias ] [ ( column_alias [ ,...n ] ) ]
| <joined_table>
| <pivoted_table>
| <unpivoted_table>
| @variable [ [ AS ] table_alias ]
| @variable.function_call ( expression [ ,...n ] )
[ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]
| FOR SYSTEM_TIME <system_time>
}
<tablesample_clause> ::=
TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )
[ REPEATABLE ( repeat_seed ) ]
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON <search_condition>
| <table_source> CROSS JOIN <table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] <joined_table> [ ) ]
}
<join_type> ::=
[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
JOIN
<pivoted_table> ::=
table_source PIVOT <pivot_clause> [ [ AS ] table_alias ]
<pivot_clause> ::=
( aggregate_function ( value_column [ [ , ]...n ])
FOR pivot_column
IN ( <column_list> )
)
<unpivoted_table> ::=
table_source UNPIVOT <unpivot_clause> [ [ AS ] table_alias ]
<unpivot_clause> ::=
( value_column FOR pivot_column IN ( <column_list> ) )
<column_list> ::=
column_name [ ,...n ]
<system_time> ::=
{
AS OF <date_time>
| FROM <start_date_time> TO <end_date_time>
| BETWEEN <start_date_time> AND <end_date_time>
| CONTAINED IN (<start_date_time> , <end_date_time>)
| ALL
}
<date_time>::=
<date_time_literal> | @date_time_variable
<start_date_time>::=
<date_time_literal> | @date_time_variable
<end_date_time>::=
<date_time_literal> | @date_time_variable
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
FROM { <table_source> [ ,...n ] }
<table_source> ::=
{
[ database_name . [ schema_name ] . | schema_name . ] table_or_view_name [ AS ] table_or_view_alias
| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]
| <joined_table>
}
<joined_table> ::=
{
<table_source> <join_type> <table_source> ON search_condition
| <table_source> CROSS JOIN <table_source>
| left_table_source { CROSS | OUTER } APPLY right_table_source
| [ ( ] <joined_table> [ ) ]
}
<join_type> ::=
[ INNER ] [ <join hint> ] JOIN
| LEFT [ OUTER ] JOIN
| RIGHT [ OUTER ] JOIN
| FULL [ OUTER ] JOIN
<join_hint> ::=
REDUCE
| REPLICATE
| REDISTRIBUTE
/*
Arguments
*/
SELECT p.ProductID, v.BusinessEntityID
FROM Production.Product AS p
JOIN Purchasing.ProductVendor AS v
ON (p.ProductID = v.ProductID);
/*
A. Using a simple FROM clause
*/
SELECT TerritoryID, Name
FROM Sales.SalesTerritory
ORDER BY TerritoryID ;
/*
B. Using the TABLOCK and HOLDLOCK optimizer hints
*/
BEGIN TRAN
SELECT COUNT(*)
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;
/*
C. Using the SQL-92 CROSS JOIN syntax
*/
wql
SELECT e.BusinessEntityID, d.Name AS Department
FROM HumanResources.Employee AS e
CROSS JOIN HumanResources.Department AS d
ORDER BY e.BusinessEntityID, d.Name ;
/*
D. Using the SQL-92 FULL OUTER JOIN syntax
*/
-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
FULL OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
E. Using the SQL-92 LEFT OUTER JOIN syntax
*/
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
LEFT OUTER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
F. Using the SQL-92 INNER JOIN syntax
*/
-- By default, SQL Server performs an INNER JOIN if only the JOIN
-- keyword is specified.
SELECT p.Name, sod.SalesOrderID
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name ;
/*
G. Using the SQL-92 RIGHT OUTER JOIN syntax
*/
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory AS st
RIGHT OUTER JOIN Sales.SalesPerson AS sp
ON st.TerritoryID = sp.TerritoryID ;
/*
H. Using HASH and MERGE join hints
*/
SELECT p.Name AS ProductName, v.Name AS VendorName
FROM Production.Product AS p
INNER MERGE JOIN Purchasing.ProductVendor AS pv
ON p.ProductID = pv.ProductID
INNER HASH JOIN Purchasing.Vendor AS v
ON pv.BusinessEntityID = v.BusinessEntityID
ORDER BY p.Name, v.Name ;
/*
I. Using a derived table
*/
SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City
FROM Person.Person AS p
INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN
(SELECT bea.BusinessEntityID, a.City
FROM Person.Address AS a
INNER JOIN Person.BusinessEntityAddress AS bea
ON a.AddressID = bea.AddressID) AS d
ON p.BusinessEntityID = d.BusinessEntityID
ORDER BY p.LastName, p.FirstName;
/*
J. Using TABLESAMPLE to read data from a sample of rows in a table
*/
SELECT *
FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;
/*
K. Using APPLY
*/
SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary
FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;
/*
L. Using CROSS APPLY
*/
USE master;
GO
SELECT dbid, object_id, query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO
/*
M. Using FOR SYSTEM_TIME
*/
SELECT DepartmentNumber,
DepartmentName,
ManagerID,
ParentDepartmentNumber
FROM DEPARTMENT
FOR SYSTEM_TIME AS OF '2014-01-01'
WHERE ManagerID = 5;
/*
N. Using the INNER JOIN syntax
*/
-- Uses AdventureWorks
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM FactInternetSales AS fis
INNER JOIN DimProduct AS dp
ON dp.ProductKey = fis.ProductKey;
/*
O. Using the LEFT OUTER JOIN and RIGHT OUTER JOIN syntax
*/
-- Uses AdventureWorks
SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM FactInternetSales AS fis
LEFT OUTER JOIN DimProduct AS dp
ON dp.ProductKey = fis.ProductKey;
/*
P. Using the FULL OUTER JOIN syntax
*/
-- Uses AdventureWorks
SELECT dst.SalesTerritoryKey, dst.SalesTerritoryRegion, fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
FULL OUTER JOIN FactInternetSales AS fis
ON dst.SalesTerritoryKey = fis.SalesTerritoryKey
ORDER BY fis.SalesOrderNumber;
/*
Q. Using the CROSS JOIN syntax
*/
-- Uses AdventureWorks
SELECT dst.SalesTerritoryKey, fis.SalesOrderNumber
FROM DimSalesTerritory AS dst
CROSS JOIN FactInternetSales AS fis
ORDER BY fis.SalesOrderNumber;
/*
R. Using a derived table
*/
-- Uses AdventureWorks
SELECT CustomerKey, LastName
FROM
(SELECT * FROM DimCustomer
WHERE BirthDate > '01/01/1970') AS DimCustomerDerivedTable
WHERE LastName = 'Smith'
ORDER BY LastName;
/*
S. REDUCE join hint example
*/
-- Uses AdventureWorks
EXPLAIN SELECT SalesOrderNumber
FROM
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM DimProduct AS dp
INNER REDUCE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
) AS dTable
ORDER BY SalesOrderNumber;
/*
T. REPLICATE join hint example
*/
-- Uses AdventureWorks
EXPLAIN SELECT SalesOrderNumber
FROM
(SELECT fis.SalesOrderNumber, dp.ProductKey, dp.EnglishProductName
FROM DimProduct AS dp
INNER REPLICATE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey
) AS dTable
ORDER BY SalesOrderNumber;
/*
U. Using the REDISTRIBUTE hint to guarantee a Shuffle move for a distribution incompatible join
*/
-- Uses AdventureWorks
EXPLAIN
SELECT dp.ProductKey, fis.SalesOrderNumber, fis.TotalProductCost
FROM DimProduct AS dp
INNER REDISTRIBUTE JOIN FactInternetSales AS fis
ON dp.ProductKey = fis.ProductKey;
Related Topics: CONTAINSTABLE DELETE FREETEXTTABLE INSERT OPENQUERY OPENROWSET Operators UPDATE WHERE
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT GROUP BY HAVING IS NULL PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WRITETEXT
Combines two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.
boolean_expression AND boolean_expression
/* A. Using the AND operator */ -- Uses AdventureWorks SELECT BusinessEntityID, LoginID, JobTitle, VacationHours FROM HumanResources.Employee WHERE JobTitle = 'Marketing Assistant' AND VacationHours > 41 ; /* B. Using the AND operator in an IF statement */ IF 1 = 1 AND 2 = 2 BEGIN PRINT 'First Example is TRUE' END ELSE PRINT 'First Example is FALSE'; GO IF 1 = 1 AND 2 = 17 BEGIN PRINT 'Second Example is TRUE' END ELSE PRINT 'Second Example is FALSE' ; GO
Related Topics: Built-in Functions Operators SELECT WHERE
Logical Operators: ALL ANY BETWEEN EXISTS IN LIKE NOT OR SOME and ANY
Negates a Boolean input.
[ NOT ] boolean_expression
/* Examples */ -- Uses AdventureWorks SELECT ProductID, Name, Color, StandardCost FROM Production.Product WHERE ProductNumber LIKE 'BK-%' AND Color = 'Silver' AND NOT StandardCost > 400; GO /* Examples: Azure SQL Data Warehouse and Parallel Data Warehouse */ -- Uses AdventureWorks SELECT ProductKey, CustomerKey, OrderDateKey, ShipDateKey FROM FactInternetSales WHERE SalesOrderNumber LIKE 'SO6%' AND NOT ProductKey < 400;
Related Topics: Built-in Functions expression Expressions Operators SELECT WHERE
Logical Operators: ALL ANY BETWEEN EXISTS IN LIKE OR SOME and ANY
Determines whether a specified value matches any value in a subquery or a list.
test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)
/*
A. Comparing OR and IN
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle = 'Design Engineer'
OR e.JobTitle = 'Tool Designer'
OR e.JobTitle = 'Marketing Assistant';
GO
/*
B. Using IN with a subquery
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person AS p
JOIN Sales.SalesPerson AS sp
ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);
GO
/*
C. Using NOT IN with a subquery
*/
-- Uses AdventureWorks
SELECT p.FirstName, p.LastName
FROM Person.Person AS p
JOIN Sales.SalesPerson AS sp
ON p.BusinessEntityID = sp.BusinessEntityID
WHERE p.BusinessEntityID NOT IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);
GO
/*
D. Using IN and NOT IN
*/
-- Uses AdventureWorks
SELECT * FROM FactInternetSalesReason
WHERE SalesReasonKey
IN (SELECT SalesReasonKey FROM DimSalesReason);
/*
E. Using IN with an expression list
*/
-- Uses AdventureWorks
SELECT FirstName, LastName
FROM DimEmployee
WHERE FirstName IN ('Mike', 'Michael');
Related Topics: ALL Built-in Functions CASE expression Expressions Operators SELECT SOME | ANY WHERE
Logical Operators: ALL ANY BETWEEN EXISTS LIKE OR SOME and ANY
About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.
