Monday, March 26, 2018

SQL - SCHEMABING thing

To check which views are using a table, you can use:

SELECT VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_NAME = 'tbl_well'

To check a certain column in a table being used by other process, you can use:

SELECT DISTINCT *
FROM sys.all_sql_modules
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%tbl_well%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%latitude%';

By including the SCHEMABINDING clause you protect the view/function from unexpected changes to the tables underneath them:

CREATE VIEW [dbo].[v_QC_well]

WITH SCHEMABINDING
...

Reference: 

https://sqlstudies.com/2014/08/06/schemabinding-what-why/

https://www.mssqltips.com/sqlservertip/1692/using-schema-binding-to-improve-sql-server-udf-performance/

No comments:

Post a Comment