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