Naming Convention Enforcer

For instance you want to enforce naming convention where objects should not start with "spData" prefix. Here is the simple unit test that is searching through object names and definitions in target database and tells if there is any naming convention issue:

 

EXEC DBTD_DROP_PROC_IF_EXISTS 'UT_NAMINGCONVENTION_ObjectPrefixCheck'

GO

 

CREATE PROC UT_NAMINGCONVENTION_ObjectPrefixCheck

       @v_Debug BIT = 0

AS

BEGIN

       DECLARE @v_SearchString VARCHAR(128) = '%spData%',

              @v_TargetDB          VARCHAR(128) = 'MyDatabase'

 

       DECLARE @v_SearchResult NVARCHAR(MAX),

              @v_SQL NVARCHAR(MAX)

                = ' INSERT INTO #ObjectsList(Id,Name)

                     SELECT id, name FROM sys.sysobjects

                     WHERE Name LIKE ''' + @v_SearchString + ''''

 

       CREATE TABLE #ObjectsList(

              Id            INT,

              Name   NVARCHAR(255)

       )

 

       CREATE TABLE #DefinitionSearchResult(

              [Object_ID] INT,

              ObjectName VARCHAR(500),--Object name with database and schema prefix

              [Type]  VARCHAR(50),

              Name  SYSNAME,       --Just an object name

              SearchString NVARCHAR(MAX)

       );

 

       --search through definition

       EXEC DBTestDriven.dbo.DBTD_SEARCH_ObjectsDefinition

              @v_SearchString = @v_SearchString,

              @v_TargetDB = @v_TargetDB,

              @v_Exclude = NULL,   --no exclusion at this time

              @v_CleanOldDefinitionSearchResult = 0,--don’t clean for now

              @v_SearchResult = @v_SearchResult OUTPUT

 

       --search through names

       EXEC DBTestDriven.dbo.DBTD_SP_EXECUTESQL

              @v_TargetDatabase = @v_TargetDB,

              @v_SQLCode = @v_SQL

 

       IF @v_Debug = 1

       BEGIN

              SELECT * FROM #DefinitionSearchResult

              SELECT * FROM #ObjectsList

              SELECT @v_SearchResult AS [@v_SearchResult]

       END

 

       EXEC DBTD_ASSERT_TABLE_HAS_NO_RECORDS

            '#ObjectsList',

            '(A) Naming convention issues'

       EXEC DBTD_ASSERT_TABLE_HAS_NO_RECORDS

            '#DefinitionSearchResult',

            '(B) Naming convention issues'

END

GO

 

And now lets run this unit test  

       --just run a unit test

       BEGIN TRAN

       EXEC UT_NAMINGCONVENTION_ObjectPrefixCheck @v_Debug = 1

       ROLLBACK

 

See Also

 

Friday, October 10, 2014 9:54:00 AM
Comments are closed on this post.
  • RSS
  • Add To My MSN
  • Add To Windows Live
  • Add To My Yahoo
  • Add To Google

Statistics

  • Entries (13)
  • Comments (0)