Fixing Inconsistent Metadata Issues in MSSQL Stored Procedures

In MS SQL, a stored procedure is T-SQL code that is precompiled and encapsulated. It is useful as you can reuse the code and execute it multiple times. It is also safe because you have better control of the actions. In addition, you can send parameters to it.

The following example shows how to create a stored procedure with parameters:

CREATE PROCEDURE dbo.getBirthdate
    @LoginID nvarchar(256)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT BirthDate
    FROM HumanResources.Employee
    WHERE LoginID = @LoginID;
END

In the above example, the stored procedure name is dbo.getBirthdate. It receives the LoginID as an input parameter and returns the BirthDate from the table HumanResources.Employee, where the LoginID is equal to the parameter specified.

In order to execute, you need to invoke the stored procedure and send the Login ID (see the below example).

exec dbo.getBirthdate 'adventure-works\andrew0'

What is Metadata in MS SQL?

Metadata in MS SQL is the data about the SQL Server objects, like stored procedures, tables, views, functions, triggers, constraints, etc. The stored procedure information is stored in system views. The following query shows the list of stored procedures in a database.

SELECT
    name,
       object_id
FROM sys.procedures

You can also use metadata functions to get this information.

SELECT
    ROUTINE_NAME
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_TYPE = 'PROCEDURE';

INFORMATION_SCHEMA.ROUTINES is a System View that contains information about stored procedures, functions, and triggers.

How to Detect Inconsistent Metadata Issues in MS SQL Stored Procedures?

There are commands to verify the database integrity, including metadata issues, in MS SQL. The DBCC CHECKDB command can detect metadata inconsistencies in the stored procedures. The following command can be used to check the integrity of a database.

DBCC CHECKDB ('stellardb')

How to Fix Inconsistent Metadata Issues in MS SQL Stored Procedures?

To fix the issues, you can recompile your stored procedure. You can use the following command to recompile your stored procedure created previously:

EXEC sp_recompile getBirthdate

Alternatively, you can try to alter the procedure and check if it can be modified. The following code shows how to do it.

ALTER PROCEDURE dbo.getBirthdate
    @LoginID nvarchar(256)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT BirthDate
    FROM HumanResources.Employee
    WHERE LoginID = @LoginID;
END

Another option is to drop the stored procedure and try to create it again. You can use the following statement to drop the stored procedure.

drop procedure dbo.getBirthdate

Alternatively, you can go to the SQL Server Management Studio (SSMS), open the Object Explorer and go to Databases > Your Database > Programmability > Stored Procedures. Then, right-click your stored procedure and select Delete.

Note: Sometimes, there are errors when the user has permission on the stored procedure but no permission on the table used by the stored procedure. In this case, executing the query can result in errors.

How to Repair Database in MS SQL?

You can try to run the DBCC CHECKDB command to repair a database. First, set the database in emergency mode. Then, set it to single-user mode with rollback immediately. After repairing the database, set it back to multi-user mode. Here are the statements:

ALTER DATABASE stellardb SET EMERGENCY;

ALTER DATABASE stellardb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DBCC CHECKDB (stellardb, REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;

ALTER DATABASE stellardb SET MULTI_USER;

What to do if None of the Solutions Works?

If nothing works, you can take the help of a powerful SQL database repair tool, such as Stellar Repair for MS SQL. This software can fix corruption in MS SQL databases, including metadata issues. It can be downloaded and installed in a few minutes. After installing the software, follow these steps:

• In SQL Server, take the database offline.

• Open the Stellar Repair for MS SQL and select the MDF file of the database. The MDF file is the physical file that contains the data. After selecting the file, click Repair.

• Select the Standard Scan option.

• After scan, you can see the entire structure of the database. Select the Stored Procedures.

• You can save the selected data in a New Database, Live Database, or other formats like CSV, Excel, and HTML.

Conclusion

Above, we have mentioned different ways to fix the inconsistent metadata issues in stored procedures in MS SQL. You can recompile the stored procedure or recreate it to try to fix the issue. If the database is corrupt, you can use the DBCC CHECKDB commands to fix the issue. Alternatively, you can repair the database using a third-party tool, like Stellar Repair for MS SQL.