How do I recompile a procedure in SQL Server?

How do I recompile a procedure in SQL Server?

To recompile a stored procedure by using sp_recompile Select New Query, then copy and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.

How do you find all stored procedures using a table?

Using below mentioned important T-SQL query, we can get the list of the tables used in the stored procedure.

1. SELECT.
2. NAME as ‘List Of Tables’
3. FROM SYSOBJECTS.
4. WHERE ID IN ( SELECT SD.DEPID.
5. FROM SYSOBJECTS SO,
6. SYSDEPENDS SD.
7. WHERE SO. NAME = ‘Sp_ListTables’ —-name of stored procedures.
8. AND SD.ID = SO.ID.

How do I list all stored procedures in SQL Server?

Get list of Stored Procedure and Tables from Sql Server database

1. For Tables: SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES.
2. For Stored Procedure: Select [NAME] from sysobjects where type = ‘P’ and category = 0.
3. For Views: Select [NAME] from sysobjects where type = ‘V’ and category = 0.

How do I recompile all views in SQL Server?

In order to rebuild all views of a SQL Server database, you could use the following script: DECLARE @view_name AS NVARCHAR(500); DECLARE views_cursor CURSOR FOR SELECT TABLE_SCHEMA + ‘. ‘ +TABLE_NAME FROM INFORMATION_SCHEMA.

How do I identify all stored procedures referencing a specific table in SQL?

How to identify all stored procedures referring a particular…

1. SELECT Name.
2. FROM sys.procedures.
3. WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%TableNameOrWhatever%’

Can we recompile view in SQL Server?

sp_recompile looks for an object in the current database only. The queries used by stored procedures, or triggers, and user-defined functions are optimized only when they are compiled. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

How to recompile all stored procedures in SQL Server?

In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan.

When to use with recompile in SQL Server?

This topic describes using the WITH RECOMPILE option when creating a procedure definition and executing an existing procedure. It also describes using the sp_recompile system stored procedure to recompile an existing procedure.

How to create a procedure in SQL Server?

Using Transact-SQL. 1 Connect to the Database Engine. 2 From the Standard bar, click New Query. 3 Copy and paste the following example into the query window and click Execute. This example creates the procedure definition.

How does a procedure benefit from a query plan?

When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes.