项目作者: bocharovf

项目描述 :
Custom code analysis rules for T-SQL
高级语言: C#
项目地址: git://github.com/bocharovf/TSqlCustomCodeAnalysis.git
创建时间: 2017-10-22T20:33:24Z
项目社区:https://github.com/bocharovf/TSqlCustomCodeAnalysis

开源协议:

下载


About

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.

Content

The solution consists of projects:

  • SqlCodeAnalysisRules - custom CA rule for VS database project. See “Run custom CA rule in Visual Studio”.
  • SqlClrAnalysisObjects - CLR UDF to run code analysis from T-SQL. See “Run custom CA rule from T-SQL”.
  • SqlAnalysisCommon - shared library with generic rule logic and resources.
  • SampleDBWithCA - sample database project with CA enabled. Just to demonstrate custom CA rule.
  • WebScraper - secondary project used to parse microsoft documentation and determine supported SQL Server versions for each function.

Run custom CA rule in Visual Studio

  1. Install SQL Server Data Tools
  2. Build solution
  3. Copy both SqlAnalysisCommon.dll and SqlAnalysisCommon.dll to VS extension folder:
    %Visual Studio Install Dir%\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\Extensions\
  4. Restart Visual Studio
  5. Run code analysis for SampleDBWithCA project.
  6. Check custom CA rule warnings for TestStoredProcedure

Run custom CA rule from T-SQL

  1. Enable CLR in SQL Server:
    1. sp_configure 'show advanced options', 1;
    2. GO
    3. RECONFIGURE;
    4. GO
    5. sp_configure 'clr enabled', 1;
    6. GO
    7. RECONFIGURE;
    8. GO
  2. Set database trustworthy:
    1. ALTER DATABASE CURRENT SET TRUSTWORTHY ON;
  3. Create required assemblies:
    ```
    CREATE ASSEMBLY [Microsoft.SqlServer.TransactSql.ScriptDom]
    FROM ‘… path to Microsoft.SqlServer.TransactSql.ScriptDom’
    WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [SqlAnalysisCommon]
FROM ‘… path to SqlAnalysisCommon.dll’
WITH PERMISSION_SET = UNSAFE;

CREATE ASSEMBLY [SqlClrAnalysisObjects]
FROM ‘… path to SqlClrAnalysisObjects.dll’
WITH PERMISSION_SET = UNSAFE;

  1. 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]

  1. 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
```