Custom code analysis rules for T-SQL
The project demonstrate ability to extend default code analysis (CA) capabilities in VS database projects with custom CA rules. It also contains CLR user-defined function (UDF) to run code analysis directly from T-SQL.
The solution consists of projects:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
CREATE ASSEMBLY [SqlAnalysisCommon]
FROM ‘… path to SqlAnalysisCommon.dll’
WITH PERMISSION_SET = UNSAFE;
CREATE ASSEMBLY [SqlClrAnalysisObjects]
FROM ‘… path to SqlClrAnalysisObjects.dll’
WITH PERMISSION_SET = UNSAFE;
4. Create UDF:
CREATE OR ALTER FUNCTION [dbo].@ruleName [nvarchar](max">GetCodeAnalysisRuleErrors, @sqlText nvarchar)
RETURNS TABLE (
[StartLine] [int] NULL,
[StartColumn] [int] NULL,
[Code] nvarchar NULL,
[Description] nvarchar NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SqlClrAnalysisObjects].[UserDefinedFunctions].[GetCodeAnalysisRuleErrors]
5. Run sample query to analyse all database objects:
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME(modules.object_id)) + ‘.’ +
QUOTENAME(OBJECT_NAME(modules.object_id)) ObjectName,
errors.StartLine,
errors.StartColumn,
errors.Code,
errors.[Description]
FROM sys.sql_modules modules
CROSS APPLY dbo.GetCodeAnalysisRuleErrors(‘IncompatibleFunctionsSql2012Rule’, modules.[definition]) errors
```