Example 1
The following queries should work “as is”, as long as they’re executed by a user with sufficient access.
In the following queries, I’m making heavy use of SQL Server’s built in metadata functions and a few system catalog views to return information about all of the PRIMARY, UNIQUE, and FOREIGN KEY constraints configured on a database.
Output for Example 1
The highlighted rows in the following queries demonstrate the use of built-in metadata functions.
/***********************************
Returns: All Keys
*************************************/
SELECT DB_NAME() as [TABLE_CATALOG],
i.name AS [INDEX_NAME],
OBJECT_SCHEMA_NAME((ic.OBJECT_ID)) AS [TABLE_SCHEMA],
OBJECT_NAME(ic.OBJECT_ID) AS [TABLE_NAME],
COL_NAME(ic.OBJECT_ID,ic.column_id) AS [COLUMN_NAME],
i.is_disabled as IS_DISABLED_KEY,
i.is_unique as IS_UNIQUE_KEY,
i.is_primary_key as IS_PRIMARY_KEY,
0 as IS_FOREIGN_KEY,
i.is_unique_constraint as IS_UNIQUE_CONSTRAINT,
null AS Reference_Table_Schema,
null AS Reference_Table_Name,
null AS Reference_Column_Name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE 1=1
and i.is_primary_key = 1 OR i.is_unique_constraint = 1
UNION
--Foreign Keys
SELECT
DB_NAME() as [TABLE_CATALOG],
f.name AS [INDEX_NAME],
OBJECT_SCHEMA_NAME(f.parent_object_id) AS [TABLE_SCHEMA], -- this
OBJECT_NAME(f.parent_object_id) AS [TABLE_NAME],
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS [COLUMN_NAME],
f.is_disabled as IS_DISABLED_KEY,
0 as IS_UNIQUE_KEY,
0 as IS_PRIMARY_KEY,
1 as IS_FOREIGN_KEY,
0 as IS_UNIQUE_CONSTRAINT,
OBJECT_SCHEMA_NAME(f.referenced_object_id) AS Reference_Table_Schema,
OBJECT_NAME (f.referenced_object_id) AS Reference_Table_Name,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS Reference_Column_Name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE f.is_ms_shipped = 0
Example 2
The following query returns all FOREIGN KEY constraints currently defined on the current database. I use this query when I just want to get a quick overview of which tables have FOREIGN KEY references defined and which ones have PRIMARY KEYS that are recipients of those constraints.
Output for Example 2
/*********************************** Returns: All Foreign Keys *************************************/ SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName, -- uses OBJECT_NAME to get Table Name COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, -- uses COL_NAME to get the column name OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id --- Snippet #019EBD51046B280B2E4E710E89E5975F (TSQL) --- The latest version of this code is at: --- https://www.sevendaysofschema.com/tsql-examples/all-key-constraints/
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.dm_db_index_operational_stats sys.foreign_key_columns OBJECT_SCHEMA_NAME sys.index_columns sys.foreign_keys sys.databases sys.columns OBJECT_NAME sys.indexes sys.objects sys.schemas sys.tables OBJECT_ID QUOTENAME SCHEMA_ID COL_NAME DB_NAME SELECT UNION WHERE DB_ID FROM AND OR
sys.dm_db_index_operational_stats (Transact-SQL) 
Returns current lowore-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.
Memory-optimized indexes do not appear in this DMV.
NOTE: sys.dm_db_index_operational_stats does not return information about memory-optimized indexes. For information about memory-optimized index use, see sys.dm_db_xtp_index_stats.
Permissions: Requires the following permissions:
- CONTROL permission on the specified object within the database
-
VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id = NULL
-
VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL
Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.
Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Also, when the database wildcard @database_id=NULL is specified, the database is omitted.
See Dynamic Management Views and Functions.
sys.dm_db_index_operational_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | 0 | NULL | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
)
/*
A. Returning information for a specified table
*/
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
/*
B. Returning information for all tables and indexes
*/
SELECT * FROM sys.dm_db_index_operational_stats( NULL, NULL, NULL, NULL);
GO
Related Topics: Dynamic Management Views and Functions Index Related Dynamic Management Views and Functions Monitor and Tune for Performance sys.allocation_units sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.dm_db_partition_stats sys.dm_os_latch_stats sys.indexes
Index Related Dynamic Management Views and Functions: sys.dm_db_column_store_row_group_physical_stats sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.dm_db_missing_index_columns sys.dm_db_missing_index_details sys.dm_db_missing_index_group_stats sys.dm_db_missing_index_groups
sys.foreign_key_columns (Transact-SQL)
Contains a row for each column, or set of columns, that comprise a foreign key.
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.
constraint_object_id int constraint_column_id int parent_object_id int parent_column_id int referenced_object_id int
Related Topics: Catalog Views Object Catalog Views Querying the SQL Server System Catalog FAQ
Object Catalog Views: sys.allocation_units sys.assembly_modules sys.check_constraints sys.columns sys.computed_columns sys.default_constraints sys.event_notifications sys.events sys.extended_procedures sys.foreign_keys sys.function_order_columns sys.hash_indexes sys.identity_columns sys.index_columns sys.key_constraints sys.masked_columns sys.memory_optimized_tables_internal_attributes sys.numbered_procedure_parameters sys.numbered_procedures sys.parameters
OBJECT_SCHEMA_NAME (Transact-SQL)
Returns the database schema name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects.
Permissions: Requires ANY permission on the object. To specify a database ID, CONNECT permission to the database is also required, or the guest account must be enabled.
OBJECT_SCHEMA_NAME ( object_id [, database_id ] )
/*
Remarks
*/
SELECT DISTINCT OBJECT_SCHEMA_NAME(object_id)
FROM master.sys.objects;
/*
A. Returning the object schema name and object name
*/
SELECT DB_NAME(st.dbid) AS database_name,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
OBJECT_NAME(st.objectid, st.dbid) AS object_name,
st.text AS query_statement
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid IS NOT NULL;
GO
/*
B. Returning three-part object names
*/
SELECT QUOTENAME(DB_NAME(database_id))
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(object_id, database_id))
, *
FROM sys.dm_db_index_operational_stats(null, null, null, null);
GO
Related Topics: Metadata Functions OBJECT_DEFINITION OBJECT_ID OBJECT_NAME Securables
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
sys.index_columns (Transact-SQL)
Contains one row per column that is part of a sys.indexes index or unordered table (heap).
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.
/*
Examples
*/
USE AdventureWorks2012;
GO
SELECT i.name AS index_name
,COL_NAME(ic.object_id,ic.column_id) AS column_name
,ic.index_column_id
,ic.key_ordinal
,ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('Production.BillOfMaterials');
object_id int index_id int index_column_id int column_id int key_ordinal tinyint partition_ordinal tinyint is_descending_key bit
Related Topics: Catalog Views CREATE INDEX Object Catalog Views Querying the SQL Server System Catalog FAQ sys.columns sys.indexes sys.objects
Object Catalog Views: sys.allocation_units sys.assembly_modules sys.check_constraints sys.columns sys.computed_columns sys.default_constraints sys.event_notifications sys.events sys.extended_procedures sys.foreign_keys sys.function_order_columns sys.hash_indexes sys.identity_columns sys.key_constraints sys.masked_columns sys.memory_optimized_tables_internal_attributes sys.numbered_procedure_parameters sys.numbered_procedures sys.parameters
sys.foreign_keys (Transact-SQL)
Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = F.
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.
\<Columns inherited from sys.objects> referenced_object_id int key_index_id int is_disabled bit is_not_for_replication bit is_not_trusted bit delete_referential_action tinyint delete_referential_action_desc nvarchar(60) update_referential_action tinyint update_referential_action_desc nvarchar(60)
Related Topics: Catalog Views Object Catalog Views Querying the SQL Server System Catalog FAQ
Object Catalog Views: sys.allocation_units sys.assembly_modules sys.check_constraints sys.columns sys.computed_columns sys.default_constraints sys.event_notifications sys.events sys.extended_procedures sys.function_order_columns sys.hash_indexes sys.identity_columns sys.key_constraints sys.masked_columns sys.memory_optimized_tables_internal_attributes sys.numbered_procedure_parameters sys.numbered_procedures sys.parameters
Contains one row per database in the instance of SQL Server.
If a database is not ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns may be NULL. If a database is OFFLINE, the corresponding row is not visible to low-privileged users. To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission, or the CREATE DATABASE permission in the master database.
Permissions: If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or the VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
IMPORTANT: By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the `VIEW ANY DATABASE permission for individual logins.
/* A. Query the sys.databases view */ SELECT name, user_access_desc, is_read_only, state_desc, recovery_model_desc FROM sys.databases; /* B. Check the copying status in SQL Database */ -- Execute from the master database. SELECT a.name, a.state_desc, b.start_date, b.modify_date, b.percentage_complete FROM sys.databases AS a INNER JOIN sys.dm_database_copies AS b ON a.database_id = b.database_id WHERE a.state = 7; /* C. Check the temporal retention policy status in SQL Database */ -- Execute from the master database. SELECT a.name, a.is_temporal_history_retention_enabled FROM sys.databases AS a;
name sysname database_id int source_database_id int owner_sid varbinary(85) create_date datetime compatibility_level tinyint collation_name sysname user_access tinyint user_access_desc nvarchar(60) is_read_only bit is_auto_close_on bit is_auto_shrink_on bit state tinyint state_desc nvarchar(60) is_in_standby bit is_cleanly_shutdown bit is_supplemental_logging_enabled bit snapshot_isolation_state tinyint snapshot_isolation_state_desc nvarchar(60) is_read_committed_snapshot_on bit recovery_model tinyint recovery_model_desc nvarchar(60) page_verify_option tinyint page_verify_option_desc nvarchar(60) is_auto_create_stats_on bit is_auto_create_stats_incremental_on bit is_auto_update_stats_on bit is_auto_update_stats_async_on bit is_ansi_null_default_on bit is_ansi_nulls_on bit is_ansi_padding_on bit is_ansi_warnings_on bit is_arithabort_on bit is_concat_null_yields_null_on bit is_numeric_roundabort_on bit is_quoted_identifier_on bit is_recursive_triggers_on bit is_cursor_close_on_commit_on bit is_local_cursor_default bit is_fulltext_enabled bit is_trustworthy_on bit is_db_chaining_on bit is_parameterization_forced bit is_master_key_encrypted_by_server bit is_query_store_on bit is_published bit is_subscribed bit is_merge_published bit is_distributor bit is_sync_with_backup bit service_broker_guid uniqueidentifier is_broker_enabled bit log_reuse_wait tinyint log_reuse_wait_desc nvarchar(60) is_date_correlation_on bit is_cdc_enabled bit is_encrypted bit is_honor_broker_priority_on bit replica_id uniqueidentifier group_database_id uniqueidentifier resource_pool_id int default_language_lcid smallint default_language_name nvarchar(128) default_fulltext_language_lcid int default_fulltext_language_name nvarchar(128) is_nested_triggers_on bit is_transform_noise_words_on bit two_digit_year_cutoff smallint containment tinyint not null containment_desc nvarchar(60) not null target_recovery_time_in_seconds int delayed_durability int delayed_durability_desc nvarchar(60) is_memory_optimized_elevate_to_snapshot_on bit is_federation_member bit is_remote_data_archive_enabled bit is_mixed_page_allocation_on bit is_temporal_retention_enabled bit catalog_collation_type int
Related Topics: ALTER DATABASE Databases and Files Catalog Views sys.database_mirroring_witnesses sys.database_recovery_status sys.dm_database_copies
Always On Availability Groups Catalog Views: sys.availability_databases_cluster sys.availability_group_listener_ip_addresses sys.availability_group_listeners sys.availability_groups sys.availability_groups_cluster sys.availability_read_only_routing_lists sys.availability_replicas sys.database_mirroring_endpoints
Returns a row for each column of an object that has columns, such as views or tables. The following is a list of object types that have columns:
- Table-valued assembly functions (FT)
-
Inline table-valued SQL functions (IF)
-
Internal tables (IT)
-
System tables (S)
-
Table-valued SQL functions (TF)
-
User tables (U)
-
Views (V)
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 name sysname column_id int system_type_id tinyint user_type_id int max_length smallint precision tinyint scale tinyint collation_name sysname is_nullable bit is_ansi_padded bit is_rowguidcol bit is_identity bit is_computed bit is_filestream bit is_replicated bit is_non_sql_subscribed bit is_merge_published bit is_dts_replicated bit is_xml_document bit xml_collection_id int default_object_id int rule_object_id int is_sparse bit is_column_set bit generated_always_type tinyint generated_always_type_desc nvarchar(60) encryption_type int encryption_type_desc nvarchar(64) encryption_algorithm_name sysname column_encryption_key_id int column_encryption_key_database_name sysname is_hidden bit
Related Topics: Catalog Views Object Catalog Views Querying the SQL Server System Catalog FAQ sys.all_columns sys.system_columns
Object Catalog Views: sys.allocation_units sys.assembly_modules sys.check_constraints sys.computed_columns sys.default_constraints sys.event_notifications sys.events sys.extended_procedures sys.function_order_columns sys.hash_indexes sys.identity_columns sys.key_constraints sys.masked_columns sys.memory_optimized_tables_internal_attributes sys.numbered_procedure_parameters sys.numbered_procedures sys.parameters
Returns the database object name for schema-scoped objects. For a list of schema-scoped objects, see sys.objects.
Permissions: Requires ANY permission on the object. To specify a database ID, CONNECT permission to the database is also required, or the guest account must be enabled.
OBJECT_NAME ( object_id [, database_id ] )
/*
Remarks
*/
USE AdventureWorks2012;
GO
SELECT DISTINCT OBJECT_NAME(object_id)
FROM master.sys.objects;
GO
/*
A. Using OBJECT_NAME in a WHERE clause
*/
USE AdventureWorks2012;
GO
DECLARE @MyID int;
SET @MyID = (SELECT OBJECT_ID('AdventureWorks2012.Production.Product',
'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);
GO
/*
B. Returning the object schema name and object name
*/
SELECT DB_NAME(st.dbid) AS database_name,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) AS schema_name,
OBJECT_NAME(st.objectid, st.dbid) AS object_name,
st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.objectid IS NOT NULL;
GO
/*
C. Returning three-part object names
*/
SELECT QUOTENAME(DB_NAME(database_id))
+ N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(object_id, database_id))
+ N'.'
+ QUOTENAME(OBJECT_NAME(object_id, database_id))
, *
FROM sys.dm_db_index_operational_stats(null, null, null, null);
GO
/*
D. Using OBJECT_NAME in a WHERE clause
*/
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(274100017);
Related Topics: Metadata Functions OBJECT_DEFINITION OBJECT_ID
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
Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
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.
/*
Examples
*/
SELECT i.name AS index_name
,i.type_desc
,is_unique
,ds.type_desc AS filegroup_or_partition_scheme
,ds.name AS filegroup_or_partition_scheme_name
,ignore_dup_key
,is_primary_key
,is_unique_constraint
,fill_factor
,is_padded
,is_disabled
,allow_row_locks
,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0
AND i.object_id = OBJECT_ID('Production.Product');
GO
object_id int name sysname index_id int type tinyint type_desc nvarchar(60) is_unique bit data_space_id int ignore_dup_key bit is_primary_key bit is_unique_constraint bit fill_factor tinyint is_padded bit is_disabled bit is_hypothetical bit allow_row_locks bit allow_page_locks bit has_filter bit filter_definition nvarchar(max)
Related Topics: Catalog Views In-Memory OLTP Object Catalog Views Querying the SQL Server System Catalog FAQ sys.filegroups sys.index_columns sys.key_constraints sys.objects sys.partition_schemes sys.xml_indexes
Mapping System Tables to System Views: sys.filegroups sys.messages sys.syscharsets sys.syslanguages
Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.
See Scalar User-Defined Functions for In-Memory OLTP.
NOTE: sys.objects does not show DDL triggers, because they are not schema-scoped. All triggers, both DML and DDL, are found in sys.triggers. sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.
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.
/*
A. Returning all the objects that have been modified in the last N days
*/
USE <database_name>;
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO
/*
B. Returning the parameters for a specified stored procedure or function
*/
USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,o.name AS object_name
,o.type_desc
,p.parameter_id
,p.name AS parameter_name
,TYPE_NAME(p.user_type_id) AS parameter_type
,p.max_length
,p.precision
,p.scale
,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, object_name, p.parameter_id;
GO
/*
C. Returning all the user-defined functions in a database
*/
USE <database_name>;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO
/*
D. Returning the owner of each object in a schema.
*/
USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'TYPE' AS entity_type
,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
,name
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type
,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
,xsc.name
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO
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 Querying the SQL Server System Catalog FAQ sys.all_objects sys.internal_tables sys.system_objects sys.triggers
Catalog Views: sys.tables
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
Related Topics: Catalog Views sys.objects
System Catalog Views: dbo.sysdac_instances sys.all_columns sys.all_objects sys.all_parameters sys.all_sql_modules 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
Returns a row for each user table in SQL Server.
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.
/*
Examples
*/
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO
\<inherited columns> lob_data_space_id int filestream_data_space_id int max_column_id_used int lock_on_bulk_load bit uses_ansi_nulls bit is_replicated bit has_replication_filter bit is_merge_published bit is_sync_tran_subscribed bit has_unchecked_assembly_data bit text_in_row_limit int large_value_types_out_of_row bit is_tracked_by_cdc bit lock_escalation tinyint lock_escalation_desc nvarchar(60) is_filetable bit durability tinyint durability_desc nvarchar(60) is_memory_optimized bit temporal_type tinyint temporal_type_desc nvarchar(60) history_table_id int is_remote_data_archive_enabled bit is_external bit history_retention_period int history_retention_period_unit int
Related Topics: Catalog Views DBCC CHECKDB DBCC CHECKTABLE In-Memory OLTP Object Catalog Views Querying the SQL Server System Catalog FAQ
Catalog Views:
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 a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
/*
Examples
*/
SELECT QUOTENAME('abc[]def');
/*
Examples: Azure SQL Data Warehouse and Parallel Data Warehouse
*/
SELECT QUOTENAME('abc def');
Related Topics: CONCAT CONCAT_WS FORMATMESSAGE PARSENAME REPLACE REVERSE String Functions STRING_AGG STRING_ESCAPE STUFF TRANSLATE
String Functions: ASCII CHAR CHARINDEX CONCAT CONCAT_WS DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE
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
Returns the name of a column from a specified corresponding table identification number and column identification number.
COL_NAME ( table_id , column_id )
/*
Examples
*/
-- Uses AdventureWorks
SELECT COL_NAME(OBJECT_ID('dbo.FactResellerSales'), 1) AS FirstColumnName,
COL_NAME(OBJECT_ID('dbo.FactResellerSales'), 2) AS SecondColumnName;
Related Topics: COL_LENGTH COLUMNPROPERTY Expressions Metadata Functions
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COLUMNPROPERTY DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID DB_NAME FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY INDEX_COL
Returns the database name.
Permissions: If the caller of DB_NAME is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
IMPORTANT: By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the VIEW ANY DATABASE permission for individual logins.
DB_NAME ( [ database_id ] )
/* A. Returning the current database name */ SELECT DB_NAME() AS [Current Database]; GO /* B. Returning the database name of a specified database ID */ USE master; GO SELECT DB_NAME(3)AS [Database Name]; GO /* C. Return the current database name */ SELECT DB_NAME() AS [Current Database]; /* D. Return the name of a database by using the database ID */ SELECT DB_NAME(database_id) AS [Database], database_id FROM sys.databases;
Related Topics: DB_ID Metadata Functions sys.databases
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COLUMNPROPERTY DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX DB_ID FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY INDEX_COL
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 database identification (ID) number.
Permissions: If the caller of DB_ID is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
IMPORTANT: By default, the public role has the VIEW ANY DATABASE permission, allowing all logins to see database information. To block a login from the ability to detect a database, REVOKE the VIEW ANY DATABASE permission from public, or DENY the VIEW ANY DATABASE permission for individual logins.
DB_ID ( [ 'database_name' ] )
/*
A. Returning the database ID of the current database
*/
SELECT DB_ID() AS [Database ID];
GO
/*
B. Returning the database ID of a specified database
*/
SELECT DB_ID(N'AdventureWorks2008R2') AS [Database ID];
GO
/*
C. Using DB_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. Return the ID of the current database
*/
SELECT DB_ID();
/*
E. Return the ID of a named database.
*/
SELECT DB_ID('AdventureWorksPDW2012');
Related Topics: DB_NAME Metadata Functions sys.databases sys.dm_db_index_operational_stats
Metadata Functions: APP_NAME APPLOCK_MODE APPLOCK_TEST ASSEMBLYPROPERTY COL_LENGTH COLUMNPROPERTY DATABASE_PRINCIPAL_ID DATABASEPROPERTYEX FILE_ID FILE_IDEX FILE_NAME FILEGROUP_ID FILEGROUP_NAME FILEGROUPPROPERTY FILEPROPERTY FULLTEXTCATALOGPROPERTY FULLTEXTSERVICEPROPERTY INDEX_COL
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
Combines two conditions. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses.
boolean_expression OR boolean_expression
/* Examples */ -- Uses AdventureWorks SELECT FirstName, LastName, Shift FROM HumanResources.vEmployeeDepartmentHistory WHERE Department = 'Quality Assurance' AND (Shift = 'Evening' OR Shift = 'Night'); /* Examples: Azure SQL Data Warehouse and Parallel Data Warehouse */ -- Uses AdventureWorks SELECT FirstName, LastName, BaseRate, HireDate FROM DimEmployee WHERE BaseRate < 10 OR HireDate >= '2001-01-01';
Related Topics: Built-in Functions expression Expressions Operators SELECT WHERE
Logical Operators: ALL ANY BETWEEN EXISTS IN LIKE NOT 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.
