SQL To Find Stored Procedure Compilation Error in SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft Corporation. It is a powerful platform for creating, managing, and querying large-scale databases. SQL Server is used by companies of all sizes to manage, store, and retrieve data for various applications.

Apart from allowing to store and manipulate data, the SQL server system provides a set of system objects/tables that are used to store metadata and configuration information about the database engine. These are known as system catalog views. These system tables contain information about various objects within the database, such as tables, indexes, stored procedures, views, constraints, etc.

Commonly Used SQL Server System Tables

sys.objects: This table contains information about all the objects in the database, such as tables, views, procedures, and functions.

sys.columns: This table contains information about all the columns in the database, such as the column name, data type, and length.

sys.indexes: This table contains information about all the indexes in the database, such as the index name, type, and columns that the index covers.

sys.partitions: This table contains information about all the partitions in the database, such as the partition number, the size of the partition, and the index associated with the partition.

sys.schemas: This table contains information about all the schemas in the database, such as the schema name and the user who owns the schema.

sys.sysdatabases: This table contains information about all the databases on the SQL Server instance, such as the database name, the owner of the database, and the creation date.

sys.procedures: This is a system view in SQL Server that contains information about stored procedures in a database. This view returns one row for each stored procedure in the database, including system procedures, user-defined procedures, and extended procedures.

SQL To Find Stored Procedure Compilation Error

-- table variable to store procedure names

DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)

-- retrieve the list of stored procedures

INSERT INTO @v(spname)

    SELECT 

        '[' + s.[name] + '].[' + sp.name + ']' 

    FROM sys.procedures sp

    INNER JOIN sys.schemas s ON s.schema_id = sp.schema_id

    WHERE is_ms_shipped = 0 and sp.name like '%rpt%'

-- counter variables

DECLARE @cnt INT, @Tot INT

SELECT @cnt = 1

SELECT @Tot = COUNT(*) FROM @v

DECLARE @spname sysname

-- start the loop

WHILE @Cnt <= @Tot BEGIN

    SELECT @spname = spname

        FROM @v

        WHERE RecID = @Cnt

    BEGIN TRY

        -- refresh the stored procedure

        EXEC sp_refreshsqlmodule @spname

    END TRY

    BEGIN CATCH

     IF XACT_STATE() <> 0 ROLLBACK;

        PRINT 'Validation failed for : ' + 

            @spname + ' Error:' + 

            ERROR_MESSAGE()

    END CATCH

    SET @Cnt = @cnt + 1

END

Why Solve Stored Procedure Compilation Errors?

Here are some reasons why it is important to address stored procedure compilation errors:

1. Consistent behavior: Stored procedure compilation error can cause it to behave inconsistently or not at all. So, it is important to resolve the compilation error to ensure that it behaves as expected.
2. Performance: When a stored procedure compilation fails, it tries to recompile every time it is executed. This causes performance issues, as the compilation process is resource-intensive. So, it is important to resolve the compilation error to improve the performance of your database application.
3. Security: Stored Procedure compilation error can be a security issue, such as missing object permission or an invalid login. By solving the compilation error, you will improve the security of your database, and it can only access the resources it is authorized to access.
4. Maintainability: Addressing compilation errors is important. Otherwise, it will accumulate over time, making it difficult to maintain and modify. By resolving compilation errors promptly, you can ensure that your database remains maintainable and easy to modify.

Conclusion

These system tables are used extensively by database administrators, developers, and other users to manage and maintain SQL Server databases and to automate repetitive tasks. It is always recommended to use the latest system catalog views, which have more features and better performance than legacy system tables.