CODEWITHSUNDEEP
sql-serverstored-proceduresparameters
bistabil
bistabil

Reputation: 57

Automating checks for inconsistencies in stored procedure parameters and referenced table's column sizes in SQL Server

In project I am currently working I am initially executing database setup by executing multiple SQL Server scripts.

These scripts among other things will create tables and stored procedures. I have around 300 tables and around 1500 stored procedures.

Recently, I've found that defined parameters in some stored procedures were defined with type of varchar(max) which caused that indexes were not used and that created performance issues.

What I am trying to do now is to check for all inconsistencies between column sizes as defined in tables and sizes of parameters in stored procedures.

I've started getting all dependencies for each table and then searching for stored procedure definitions in script files and checking all sizes.

Other than the fact that some of those procedures have 20+ parameters which checking is very prone to errors, if I go like this I will cover some procedures multiple times because some procedure can depend on multiple tables and I can't just check for cases where varchar(max) is used I have also to check cases where size is defined but maybe it's not same as in table.

Is there a tool that could assist me with this task?

Upvotes: 1

Views: 30

Answers (1)

marc_s
marc_s

Reputation: 754508

This query against the sys catalog views can produce the procedures and its parameters which are of type (n)varchar(max) (you might also want to check for (n)text as well and replace those - they're deprecated anyway):

SELECT
    ProcedureName = pr.name,
    ParameterName = para.name,
    TypeName = t.name,
    ParaMaxLength = para.max_length
FROM    
    sys.procedures pr
LEFT OUTER JOIN 
    sys.all_parameters para ON para.object_id = pr.object_id
LEFT OUTER JOIN 
    sys.types t ON para.system_type_id = t.system_type_id
WHERE
    t.name IN ('varchar', 'nvarchar')
    AND para.max_length = -1

Upvotes: 1

Related Questions