Transact-SQL syntax not supported in Azure SQL Database

 

Comparing SQL Server and Azure SQL Database: T-SQL Compatibility and Key Differences

When working with Microsoft SQL Server and Azure SQL Database, developers will find that most Transact-SQL (T-SQL) features behave similarly across both platforms. Core functionalities such as data types, operators, and standard SQL functions—like string, arithmetic, logical, and cursor operations—are fully supported in both environments. However, there are certain differences worth understanding, especially when migrating applications or developing hybrid solutions.

---

Core T-SQL Features and Differences

Fully Supported T-SQL Features

For general application development, the majority of standard T-SQL features are identical in both environments. These include:

• Common data types and operators

• Arithmetic, logical, and string functions

• Cursors and control-of-flow constructs

However, differences emerge in system-level operations and administrative features.

---

Notable Differences in Standard T-SQL

While basic SQL usage remains consistent, some areas of T-SQL functionality differ between SQL Server and Azure SQL Database:

Key Differences

• Collation Rules: System object collation may vary.

• Authentication: Azure SQL does not support Windows authentication. Instead, it supports Azure Active Directory (AAD) authentication.

• Connection Endpoints: Some endpoint-related statements are unsupported.

• Cross-Database Queries: Direct multi-database queries using three- or four-part names are not supported, but elastic queries can be used for read-only access across databases.

• Ownership Chaining: Features like TRUSTWORTHY and cross-database ownership chaining are not available.

• Security Context: EXECUTE AS LOGIN is not supported. Use EXECUTE AS USER instead.

• Encryption: Supported with limitations—extensible key management is not available.

• Events and Notifications: Event notifications and query notifications are not supported.

• File Management: File placement, sizing, and management are handled by Azure and cannot be manually configured.

• Log Reader Features: Features depending on log readers (e.g., Change Data Capture) are not available.

• Debugging: T-SQL debugging is not supported.

• USE Statement: Changing the database context requires a new connection.

---

Advanced T-SQL Feature Differences

Some advanced features that rely on hardware access or deep server configuration are either limited or unavailable in Azure SQL Database:

• High Availability Configurations: Managed directly via Azure—traditional features like Always On or log shipping are unavailable.

• Server-Level Triggers: Logon and server-scoped triggers are not supported.

• Trace Flags: These are mostly deprecated or replaced by compatibility levels.

• CLR Integration: .NET CLR features are not supported.

• SQL Server Agent: Job scheduling must be handled using alternatives like Azure Automation or PowerShell.

• SQL Server Audit: Use Azure’s built-in auditing capabilities instead.

• SQL Trace: Unsupported—use Extended Events or auditing.

• Remote Procedure Transactions: Features like SET REMOTE_PROC_TRANSACTIONS are not available.

• Administrative Procedures: System stored procedures like sp_addmessage, sp_helpuser, or sp_migrate_user_to_contained have limited or no support.

• DMVs and Server Roles: Some server-level views and permissions are replaced with database-scoped alternatives.

• Configuration Settings: sp_configure options are replaced by ALTER DATABASE SCOPED CONFIGURATION.

• Semantic Search and FileTable: Not supported in Azure SQL Database.

• Linked Server Features: Statements like OPENQUERY, OPENROWSET, or four-part names are restricted.

• Resource Management: You must configure compute and storage through service tiers instead of hardware tuning.

---

T-SQL with Partial Support or Restrictions

While certain syntax is still available, it may be limited in scope or functionality:

• Database Creation and Alteration: Over 30 options are unavailable in Azure SQL for CREATE and ALTER DATABASE commands.

• FileTable Options: Not supported due to the absence of FILESTREAM.

• Login Management: Only a subset of options are supported for creating and altering logins in Azure.

---

Learn More

For a detailed and continuously updated list of supported features, check Microsoft’s documentation:
🔗 Transact-SQL in Azure SQL Database

How to find log file used space in SQL server

How to find the log file used space in SQL Server

Check the log size in percentage across all databases using the following SQL query:

DBCC SQLPERF(LOGSPACE)

The result of the query provides details about the log space used by different databases. Below is an example of how the output might look:

Database Name   Log Size (MB)   Log Space Used (%)   Status

-----------------------------------------------------------

master          1.992188         43.52941            0

tempdb          15.99219         2.393747            0

model           7.992188         18.23069            0

msdb            28.80469         7.770545            0

This output shows the log size and the percentage of space used for each database. The data is useful for monitoring and maintaining the health of your SQL Server databases.

Top Procedures By Total Logical Writes

 Top Procedures By Total Logical Writes

Understanding "logical writes" is crucial for database performance, as they directly impact both memory and disk I/O. Essentially, logical writes refer to how many pages are written to the buffer cache in memory. High logical writes can indicate a number of things, including inefficient queries that touch too many data pages, or issues with index usage. Monitoring these can help you pinpoint queries that are putting undue pressure on your system's resources.

SELECT TOP (25) p.name AS [SP Name]
 ,qs.total_logical_writes AS [TotalLogicalWrites]
 ,qs.total_logical_writes / qs.execution_count AS [AvgLogicalWrites]
 ,qs.execution_count
 ,ISNULL(qs.execution_count / DATEDIFF(Minute, qs.cached_time, GETDATE()), 0) AS [Calls/Minute]
 ,qs.total_elapsed_time
 ,qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,FORMAT(qs.last_execution_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Last Execution Time]
 ,FORMAT(qs.cached_time, 'yyyy-MM-dd HH:mm:ss', 'en-US') AS [Plan Cached Time]
-- ,qp.query_plan AS [Query Plan] -- Uncomment if you want the Query Plan 
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK) ON p.[object_id] = qs.[object_id]
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.database_id = DB_ID()
 AND qs.total_logical_writes > 0
 AND DATEDIFF(Minute, qs.cached_time, GETDATE()) > 0
ORDER BY qs.total_logical_writes DESC
OPTION (RECOMPILE);

The SQL query above helps identify the top 25 stored procedures by their total logical writes within your current database. This can be a valuable tool for pinpointing potential performance bottlenecks and optimizing your SQL Server environment. Remember that while this query is powerful, it's always a good idea to analyze the query plan and other metrics to get a complete picture of your query's performance.

Sql Server code template & guideline

 

Best Practices for Writing SQL Code: Stored Procedures, Views, and Functions

When working with large development teams or frequent production releases, it's essential to follow a consistent coding structure in SQL Server. This is particularly important in environments with restricted permissions where operations like DROP or CREATE may be limited for security or governance reasons.

The following guide outlines a standardized approach to writing SQL code — especially stored procedures, views, and functions — which enhances maintainability, consistency, and collaboration across teams.

---

📌 Sample SQL Procedure Template

Below is a template you can adapt for writing stored procedures:

/******************************************************************
* DATABASE        : OMAPINTERNET
* PROCEDURE NAME  : usp_SelectAllResults
* CREATED ON      : 04/03/2009
* AUTHOR          : Homer Simpson
* DESCRIPTION     : This procedure returns all results for a given NDC.
* WORK ORDER      : #13798
*******************************************************************
* MODIFICATIONS:
* DATE            : DEVELOPER         : CHANGES
* 08/01/2013      : Himanshu Patel    : Added Indicator column
******************************************************************/

---

✅ Recommended Coding Standards for SQL Procedures

To ensure clarity and best practices in SQL development, follow these guidelines:

• Use CamelCase for naming procedures and variables.
• Implement error handling using TRY...CATCH.
• Avoid cursors whenever possible; use set-based operations instead.
• Indent your code for better readability.
• Add blank lines between logical sections of your code.
• Use ANSI JOINs rather than old-style joins.
• Write clear and meaningful comments.
• Avoid using DROP or CREATE statements inside procedures.
• Declare all variables at the top of the procedure.
• Do not use column numbers in ORDER BY clauses — use column names instead.
• Avoid the use of the GOTO statement.
• Always use primary keys in WHERE clauses for UPDATE or DELETE statements.
• Add SET NOCOUNT ON at the beginning to reduce unnecessary result sets.
• Avoid SELECT *; explicitly define columns.
• Keep transactions short and commit as soon as possible.
• Use BEGIN TRANSACTION and COMMIT blocks for insert/update operations.
• Use sp_executesql instead of EXEC(@sql) for dynamic SQL.
• Avoid using WITH RECOMPILE unless absolutely necessary.
• Never use SQL reserved keywords as object names.
• Structure schemas so that each module has its own schema if the number of tables is large.
• Implement server-side paging to minimize network traffic.

---

🔠 Standard Variable Prefixes

To improve code readability and maintain consistency, use the following naming conventions for variable prefixes based on data type:

No	Data Type	Prefix	Notes
1	tinyint	@ti
2	smallint	@sm
3	int	@in
4	bigint	@bi
5	numeric	@nu
6	hierarchyid	@hi
7	bit	@bl
8	date	@Dt
9	time	@tm
10	datetimeoffset	@do
11	datetime	@Dt
12	datetime2	@Dt
13	money	@mo
14	smallmoney	@Sm
15	decimal	@De
16	float	@fl
17	char	@Ch
18	nchar	@nc
19	varchar	@Vc
20	nvarchar	@Nv
21	uniqueidentifier	@Ui
22	text	@Tx
23	ntext	@Nt
24	sql_variant	@Sv
25	varbinary	@Vb
26	image	@Im
27	xml	@Xm

Dedicated hosting provider name with Price calculator

 

Cloud Hosting Providers and Pricing Calculators

When selecting a cloud hosting service, cost estimation can vary greatly depending on several factors, including:

• Processing power (CPU)

• Storage type, size, and performance

• Memory allocation

• High availability or redundancy configurations

• Licensing for OS or software (e.g., Windows, Oracle)

• Usage patterns (duration, scalability needs, etc.)

Each cloud service provider offers a wide range of configurable services and pricing calculators to help estimate costs. Fortunately, most configurations can be modified even after deployment, offering great flexibility.

---

Google Cloud Platform (GCP)

Key Services:

• Compute Engine, App Engine, Kubernetes Engine, Cloud Run

• Cloud Storage, Networking, Cloud SQL, BigQuery, BigQuery ML

• Firestore, Datastore, Dataflow, Dataproc, Pub/Sub, Bigtable

• Cloud Functions, Cloud Endpoints, Stackdriver, Cloud DNS

• Vision API, Speech API, Translate API, Natural Language API

• Machine Learning, Cloud Spanner, KMS, CDN

• Healthcare API, IoT Core, Dialogflow, Cloud Composer

• Identity Platform, Data Loss Prevention, Memstore, and more

Pricing Calculator:
https://cloud.google.com/products/calculator/

---

Amazon Web Services (AWS)

Key Services:

• Amazon EC2, S3, Route 53, CloudFront

• RDS, DynamoDB, ElastiCache, Redshift, Glacier

• Elastic Load Balancing, CloudWatch, CloudSearch, Kinesis

• SES, SNS, SQS, SWF, Elastic Transcoder

• WorkSpaces, WorkDocs, Directory Service

• Elastic MapReduce, SimpleDB, VPC, Elastic File System

• Snowball, Direct Connect, and a wide range of other tools

Pricing Calculator:
https://calculator.s3.amazonaws.com/index.html

---

Microsoft Azure

Azure offers a comprehensive range of cloud solutions across compute, storage, networking, AI, analytics, databases, and more.

Service List:
https://azure.microsoft.com/en-us/services/

Pricing Calculator:
https://azure.microsoft.com/en-us/pricing/calculator/

---

IBM Cloud

IBM Cloud offers a flexible pricing structure depending on the type of virtual server or cloud service selected. They provide separate links for services, catalog, and pricing.

• Virtual Servers

• Service Catalog

• Pricing Overview

---

Rackspace

Rackspace provides managed cloud solutions and includes a simple pricing calculator for various configurations.

Pricing Calculator:
https://www.rackspace.com/calculator

---

Dimension Data

Dimension Data (now part of NTT Ltd.) also offers cloud pricing based on specific service selections.

Rate Card:
https://www.dimensiondata.com/en/legal/cloud-rate-card

---

Each provider has its own pricing structure and service model. When planning your cloud infrastructure, it's best to use the official calculators and adjust configurations according to your budget, performance requirements, and long-term scalability needs.

Important SQL Server Database Engine Interview Questions and Answers

Here I am trying to provide some important SQL Server database engine interview questions and answers.

What is T-SQL?

T-SQL stands for Transact Structured Query Language. It is an extension of SQL functionality supported by Microsoft SQL Server.

How can T-SQL statements be written and submitted to the Database engine?

T-SQL statements can be written and submitted to the Database engine in the following ways:

• By using the SQLcmd Utility
• By using the SQL Server Management Studio
• By connecting from an application that you create

What is the difference between TRUNCATE and DELETE statements?

The difference between TRUNCATE and DELETE statements is as follows:

• TRUNCATE is used for the unconditional removal of data records from Tables. Truncate operations are not logged.
• DELETE is used for conditional removal of data records from Tables. These operations are logged.
• TRUNCATE is faster than DELETE.
• Truncate command resets the identity value.

What does the T-SQL command IDENT_CURRENT do?

The T-SQL command IDENT_CURRENT returns the last identity value produced for a specified table or view. The last identity value created can be for any session and any scope.

What does the T-SQL command IDENT_INCR do?

The T-SQL command IDENT_INCR returns the increment value mentioned during the formation of an identity column in a table or view that has an identity column.

Can we import data directly from T-SQL commands without using SQL Server Integration Services? If yes, what are the commands?

Yes, it is possible to import data directly from T-SQL commands without using SQL Server Integration Services. These commands include:

• BCP
• OpenRowSet
• Bulk Insert
• OPENQUERY
• OPENDATASOURCE
• Linked Servers

What is a sub-query?

A sub-query is a query that can be nested inside the main query, such as Select, Update, Insert, or Delete statements. Properties of sub-queries include:

• A subquery should not have an ORDER BY clause.
• A subquery should be placed in the right-hand side of the comparison operator of the main query.
• A subquery should be enclosed in parentheses because it needs to be executed first before the main query.
• More than one sub-query can be included.

What are dynamic queries in T-SQL?

Dynamic queries in T-SQL are those queries designed on the fly or at runtime using variables or using CTEs (Common Table Expressions) or other sources. We use EXECUTE function or SP_EXECUTESQL stored procedure to execute such queries.

What are ROLLUP and CUBE in T-SQL?

ROLLUP and CUBE are grouping sets used along with the GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.

What are the Join Types in T-SQL?

Join Types in T-SQL are as follows:

• Inner join
• Outer join
• Left outer join
• Right outer join
• Left outer join with Exclusions
• Right outer join with Exclusions
• Full outer join
• Full outer joins with Exclusions
• Cross join

What are the String functions available in T-SQL?

String functions available in T-SQL are:

• Left
• Right
• Ltrim
• Rtrim
• Substring
• Replace
• Stuff

What is OFFSET-FETCH filter in T-SQL?

In T-SQL, OFFSET-FETCH filter is designed similar to TOP but with an extra element. It helps to define how many rows you want to skip before specifying how many rows you want to filter.

What is Patindex?

The PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression or zero if the pattern is not found.

PATINDEX('%pattern%', expression)

How To Create a Temporary Table? How Do We Apply Nonclustered Index? What Is Nolock? When And Where Is Nolock Applied Normally?

Two ways of creating a temporary table with a non-clustered index applied to it. Also, an example shows how to apply "nolock." NOLOCK is normally applied while querying on production servers. This would make the records being queried sharable on the table. It allows other queries to query the same record in parallel. However, it might return junk data in certain cases because the select query might query the table while other insertions or updates are being performed on the table.

CREATE TABLE #tmpTable
(
  OfficeName varchar(50),
  officeid int,
  CustID int,
  AgentID int,
  mlsid varchar(4),
  RequestMoreDetails int null,
  Emails int null
)
CREATE NONCLUSTERED INDEX #IX_tmpTable ON #tmpTable(AgentID)

SELECT
  OfficeName,
  officeid,
  o.CustID,
  AgentID,
  o.mlsid,
  PrintBrochure_Views = null,
  RequestMoreDetails = null,
  Emails = null
INTO #table1 FROM Offices o
LEFT JOIN dbo.table2 t2 WITH (NOLOCK)
ON t2.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #table1(AgentID)

What Tools Do You Use For Performance Tuning?

Several tools are useful when performance tuning your SQL Server applications, including:

• Query Analyzer
• Profiler
• Index Wizard
• Performance Monitor

What Is SQL Profiler?

SQL Profiler is a tool that allows a system administrator to monitor events in SQL Server. It is mainly used to capture and save data about each event of a file or table for analysis.

What is a Recursive Stored Procedure?

SQL Server supports recursive stored procedures, which call themselves. A recursive stored procedure can be defined as a method of problem-solving wherein the solution is arrived at repetitively. It can nest up to 32 levels.

What are the Differences Between Local and Global Temporary Tables?

Local and global temporary tables differ as follows:

• Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.

CREATE TABLE #

• Global temporary tables are visible to all users and are deleted when the connection that created it is closed.

CREATE TABLE ##

What is a CHECK Constraint?

A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. The CHECK constraint enforces data integrity.

Can SQL Servers Link to Other Servers?

SQL Server can be connected to any database that has an OLE-DB provider. For example, Oracle has an OLE-DB provider that can be used to connect with SQL Server.

How to check the writeable copy of the database in the availability group SQL server

How to Verify Writable Copies of Databases in an SQL Server Availability Group

This script is useful to check before executing any automation processes, ensuring you're writing to a valid, writable replica.

Checking the Writable Replica for a Specific Database

Before performing any database automation, it's critical to verify that you're operating on a writable replica. Below is a T-SQL script that can be used to check whether a database is on a writable replica. If the database is not writable, the script will exit gracefully:

if (dbo.fn_is_writeable_replica('DatabaseName') = 0)
BEGIN
    PRINT 'EXITING GRACEFULLY';
    THROW 51000, 'This is not a writable replica', 1;
END
    

List Writable Databases from All Availability Groups

To list all the writable databases in your Availability Groups, use the following query. This script retrieves databases that are marked as primary and not part of any distributed availability group:

-- List writable databases from all availability groups
SELECT 
    ars.role_desc, 
    ag_name = ag.[name], 
    adc.[database_name]
FROM sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states ars ON ars.group_id = ag.group_id
JOIN sys.availability_databases_cluster adc ON adc.group_id = ag.group_id
WHERE ars.is_local = 1
  AND ars.role_desc = 'PRIMARY'
  AND NOT EXISTS (
    SELECT 1
    FROM sys.availability_groups dag
    JOIN sys.availability_replicas fwd ON fwd.group_id = dag.group_id
    JOIN sys.availability_groups ag2 ON ag2.name = fwd.replica_server_name
    JOIN sys.availability_databases_cluster db ON db.group_id = ag2.group_id
    WHERE dag.is_distributed = 1
      AND db.[database_name] = adc.[database_name]
  )
    

This query will list all the writable databases in your SQL Server Availability Groups, ensuring that you're only interacting with the primary replica that is not part of any distributed groups.

How to add active directory linked server

 How to add Active Directory-linked server

This is an example of how to set up a linked server to Active Directory.

USE [master]
GO

/****** Object:  LinkedServer [ADSI] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI'
 ,@srvproduct = N'Active Directory Services 2.5'
 ,@provider = N'ADSDSOObject'
 ,@datasrc = N'adsdatasource'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'collation compatible'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'data access'
 ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'dist'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'pub'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'rpc'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'rpc out'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'sub'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'connect timeout'
 ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'collation name'
 ,@optvalue = NULL
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'lazy schema validation'
 ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'query timeout'
 ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'use remote collation'
 ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'ADSI'
 ,@optname = N'remote proc transaction promotion'
 ,@optvalue = N'true'
GO

Example query to retrieve user details from Active Directory.

SELECT *
FROM openquery(adsi, '
select employeeID
 ,sAMAccountName
 ,sAMAccountType
 ,distinguishedName
 ,displayName
 ,cn
 ,givenName
 ,middlename
 ,initials
 ,title
 ,department
 ,mail
 ,streetAddress
 ,c
 ,co
 ,st
 ,l
 ,postalCode
 ,homePhone
 ,mobile
 ,telephoneNumber
 ,company
 ,manager
 ,info
 ,homeDrive
 ,homeDirectory
 ,whenChanged
 ,whenCreated
 ,wWWHomePage
 ,internationalISDNNumber
 ,accountExpires
 ,comment
 ,assistant
 ,countrycode
 ,lastlogon
 ,legacyExchangeDN
 ,userPrincipalName
 ,logoncount
 ,physicalDeliveryOfficeName
 ,ou
 ,primaryGroupID
 ,scriptPath
 ,textEncodedORAddress
 ,facsimileTelephoneNumber
 ,modifyTimeStamp
 ,objectCategory
 ,uSNChanged
 ,uSNCreated
from ''LDAP://ad.MyAdServer.com''
where objectCategory = ''Person''
      and objectClass = ''user''
') AS tab

Refresh all views in a database

 

Refreshing SQL Server Views Automatically

When working with SQL Server, views can become outdated if the underlying tables or objects they depend on are modified. This is especially true for non-schema-bound views—those that don’t explicitly enforce dependency checks on referenced objects. Fortunately, SQL Server provides tools to refresh the metadata of these views, ensuring that they continue to function correctly.

Script to Refresh All Views in a Database

The following T-SQL script automates the process of refreshing all non-schema-bound views in a selected database. It loops through each eligible view and executes the sp_refreshview stored procedure.

DECLARE @ActualView VARCHAR(255);
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT DISTINCT s.[Name] + '.' + o.[Name] AS ViewName
FROM [sys].objects o
JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID 
WHERE o.[type] = 'V'
  AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
  AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1;

OPEN viewlist;
FETCH NEXT FROM viewlist INTO @ActualView;

WHILE @FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXECUTE sp_refreshview @ActualView;
    END TRY
    BEGIN CATCH
        PRINT 'View ' + @ActualView + ' cannot be refreshed.';
    END CATCH;

    FETCH NEXT FROM viewlist INTO @ActualView;
END;

CLOSE viewlist;
DEALLOCATE viewlist;

This script:

• Selects all user-defined, non-schema-bound views
• Iteratively refreshes each view using sp_refreshview
• Handles errors gracefully using TRY...CATCH

Refreshing a Specific View

If you want to refresh a single view manually, you can run:

EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

Refresh Views Dependent on a Specific Object

Sometimes, you may want to refresh only those views that depend on a particular table or object. Here’s how you can identify and generate sp_refreshview commands for them:

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V'
  AND sed.referenced_id = OBJECT_ID('Person.Person');

Replace 'Person.Person' with the name of the object that has changed. This query will list the commands needed to refresh only the affected views.

Learn More

For more details, see the official Microsoft documentation:
sp_refreshview (Transact-SQL) – Microsoft Docs