/*
	Pentana Audit - Find rows with empty Name

	Purpose of script:

		Some tables contain columns for Ref, Title and Name.  Name is a concatenation of Ref and Title, and in some cases is left blank in error.
		This script finds any row in any relevant table where the Name is incorrectly blank.

		More details at KB: Pentana Audit:  Record with Ref and Title has a blank Name
		
*/

-- Build a temp table to hold the result
	CREATE TABLE #EmptyNames (
	TABLE_NAME NVARCHAR(128), 
	ID INT, 
	Ref NVARCHAR(50), 
	Title NVARCHAR(500),
	Name NVARCHAR(555));


-- Find all tables that contain Name, Ref and Title columns (exclude logs and the Question table), and insert into the temp table a row for each with an
-- empty or NULL name
	DECLARE @SQL1 NVARCHAR(MAX);  
		DECLARE @TableName NVARCHAR(128);

		DECLARE [TablesWithName] CURSOR FOR
			SELECT TABLE_NAME from INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('Ref', 'Title', 'Name') AND Table_Name NOT LIKE '%_log' AND TABLE_NAME <> 'Question'
				GROUP BY TABLE_NAME
				HAVING COUNT(*) = 3;

		OPEN [TablesWithName]
		FETCH [TablesWithName] INTO @TableName

		WHILE @@FETCH_STATUS = 0	
			BEGIN	  	

		PRINT @TableName
				SET @SQL1 = 'INSERT INTO #EmptyNames SELECT ''' + @TableName + ''', ID, Ref, Title, Name FROM ' + @TableName + ' WHERE Name IS NULL OR NAME = '''';'
				EXEC (@SQL1);

				FETCH [TablesWithName] INTO @TableName
			END

		DEALLOCATE [TablesWithName]
GO

-- Show the result
	SELECT * FROM #EmptyNames;

-- Tidy up
	DROP TABLE #EmptyNames;
	GO