I added cus infront of the name so it won't be removed when we do patches/upgrades:
?
USE [DATABASENAME]
GO
/*** Object: StoredProcedure [dbo].[cusFindChartOpenIssues] Script Date: 08/26/2014 13:11:29 ***/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[cusFindChartOpenIssues]
@TableToTest varchar(60) = NULL,
@PID numeric(19,0) = NULL,
@CheckNullsOnly bit = NULL
AS
SET NOCOUNT ON
DECLARE @PidSelect NVARCHAR(50) = ''
DECLARE @FlagPidSelect NVARCHAR(50) = ''
IF @PID IS NOT NULL
BEGIN
SET @PidSelect = ' AND PID = ' + CAST(@pid as varchar)
SET @FlagPidSelect = ' AND ContextID1 = ' + cast(@PID as varchar)
END
IF @CheckNullsOnly IS NULL
BEGIN
DECLARE @Temp TABLE(RowId INT IDENTITY(1,1), SchemaName sysname, TableName sysname, ColumnName SysName, DataType VARCHAR(100), PidColumn VARCHAR(40), DataFound BIT)
IF @TableToTest IS NULL
BEGIN
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
And C.COLUMN_NAME <> 'RegNote' -- only computed column outside of PatientVisitProcs
And C.Table_Name IN ('PatientProfile',
'ALLERGY',
'APPT',
'ASSESS',
'DIRECTIV',
'DOCIMAGES',
'DOCUMENT',
'FLAG',
'InteractionOverride',
'Med_Override',
'MEDDX',
'MEDICATE',
'MEDICATIONHISTORY',
'OBS',
'ORDDX',
'ORDERS',
'ORDTOCOMPLETE',
'PatientContacts',
'PatientRelationship',
'PRESCRIB',
'PROBLEM',
'PROBLEMLISTVIEWS',
'UI_LAYOUT')
END
ELSE -- Tablename specified
INSERT INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM Information_Schema.Columns AS C
INNER Join Information_Schema.Tables AS T
ON C.Table_Name = T.Table_Name
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE Table_Type = 'Base Table'
And C.Table_Name = @TableToTest
And C.COLUMN_NAME <> 'RegNote' -- only computed column outside of PatientVisitProcs
And Data_Type In ('ntext','text','nvarchar','nchar','varchar','char')
update @Temp SET PidColumn = 'Pid' where TableName <> 'Flag'
update @Temp SET PidColumn = 'ContextId1' where TableName = 'Flag'
DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)
SET @SQLTemplate = 'If Exists(Select top 1 *
From ReplaceTableName Where '
IF @PID is not null
BEGIN
SET @SQLTemplate = @SQLTemplate + 'ReplacePidColumn = ' + CAST(@pid as varchar)
END
ELSE
SET @SQLTemplate = @SQLTemplate + '1=1'
SELECT @SQLTemplate = @SQLTemplate + -- tried doing this with function showInvalidXMLChars but performance was terrible
' AND ( Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(02)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(04)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(06)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(08)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(12)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(15)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(17)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(19)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(21)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(23)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(25)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(27)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(29)+''%''
or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), [ReplaceColumnName]) like ''%''+CHAR(31)+''%''
))
Set @DataExists = 1
Else
Set @DataExists = 0'
,
@PARAMETERS = '@DataExists Bit OUTPUT',
@i = 1
SELECT @i = 1, @MAX = MAX(RowId)
FROM @Temp
WHILE @i <= @MAX
BEGIN
SELECT @SQL = REPLACE(REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)), 'ReplaceColumnName', ColumnName), 'ReplacePidColumn', PidColumn)
FROM @Temp
WHERE RowId = @i
--PRINT @SQL
EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT
IF @DataExists =1
UPDATE @Temp SET DataFound = 1 WHERE RowId = @i
SET @i = @i + 1
END
DECLARE @Temp_TableName varchar(300) -- jmm Aug 16, 2013
SELECT @Temp_TableName = MIN(TableName) FROM @Temp -- jmm Aug 16, 2013
PRINT 'Tables / Columns with bad characters ('+@Temp_TableName+')'
SELECT TableName, ColumnName
FROM @Temp
WHERE DataFound = 1
PRINT 'Script to Correct -- Please review before executing any queries'
-- Note: tried using replace instead of function call for performance but that does not work with text data type
SELECT 'update ' + TableName + ' set ' + ColumnName +
' = dbo.stripInvalidXMLChars(' + ColumnName + ') where ('
+ 'Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(02)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(04)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(06)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(08)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(12)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(15)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(17)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(19)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(21)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(23)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(25)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(27)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(29)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(31)+''%'')'
+ @PidSelect
FROM @Temp
WHERE DataFound = 1
AND TableName <> 'Flag'
SELECT 'update ' + TableName + ' set ' + ColumnName +
' = dbo.stripInvalidXMLChars(' + ColumnName + ') where ('
+ 'Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(01)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(02)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(03)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(04)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(05)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(06)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(07)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(08)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(11)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(12)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(14)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(15)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(16)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(17)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(18)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(19)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(20)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(21)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(22)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(23)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(24)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(25)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(26)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(27)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(28)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(29)+''%''
or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(30)+''%'' or Convert(nVarChar(4000), ' + ColumnName + ') like ''%''+CHAR(31)+''%'')'
+ @FlagPidSelect
FROM @Temp
WHERE DataFound = 1
AND TableName = 'Flag'
END -- @CheckNullsOnly is NULL
-- Check for NULLs in specific fields
IF @PID is NULL
BEGIN
IF EXISTS (SELECT TOP 1 ID from FLAG where MESSAGE is null)
SELECT 'update Flag set Message = '''' where Message is NULL'
IF EXISTS (SELECT TOP 1 SDID from Document where ConfType is null)
SELECT 'update Document set ConfType = 0 where ConfType is NULL'
END
ELSE
BEGIN
IF EXISTS (SELECT TOP 1 ID from FLAG where ContextId1 = @PID AND MESSAGE is null)
SELECT 'update Flag set Message = '''' where Message is NULL' + @FlagPidSelect
IF EXISTS (SELECT TOP 1 SDID from Document where PID = @PID AND ConfType is null)
SELECT 'update Document set confType = 0 where confType is NULL' + @PidSelect
FROM DOCUMENT where CONFTYPE is NULL and PID = @PID
END
GO