Find Replicated columns of certain data types

-- Script to scan thru all articles in all publications 
-- for certain columns.
CREATE TABLE #tmpArticleColumns
(
ColumnID int,
ColumnName sysname,
PublishedFlag bit
)
GO 

CREATE TABLE #tmpArticlesWithTextTypes
(
 PublicationDB sysname
,PublicationName sysname
,ArticleName sysname
,TableName sysname
,ColumnName sysname
,DataType  sysname
)
GO

SET NOCOUNT ON

DECLARE @lngPKID int
DECLARE @lngMaxPKID int
DECLARE @strPublisherDB sysname
DECLARE @strPublicationName sysname
DECLARE @strArticleName sysname
DECLARE @strSourceObject sysname
DECLARE @strSQL varchar(1024)

SELECT
 IDENTITY(int) AS PKID
,DB_NAME() AS PublisherDB
,p.[name] AS PublicationName
,a.[name] AS ArticleName
,OBJECT_NAME(a.objid) AS SourceObject
INTO
#tmpArticles
FROM
dbo.syspublications AS p
INNER JOIN dbo.sysarticles AS a ON
(p.pubid = a.pubid)
WHERE
OBJECTPROPERTY(a.objid, 'IsTable') = 1

SELECT @lngMaxPKID = MAX(PKID) FROM #tmpArticles
SET @lngPKID = 0

WHILE @lngPKID <= @lngMaxPKID BEGIN
-- Get the next set of article info
SELECT
 @strPublisherDB = PublisherDB
,@strPublicationName = PublicationName
,@strArticleName = ArticleName
,@strSourceObject = SourceObject
FROM
#tmpArticles
WHERE
PKID = @lngPKID

DELETE FROM #tmpArticleColumns

SET @strSQL = 'INSERT INTO #tmpArticleColumns EXEC [dbo].sp_helparticlecolumns @publication = ''' + @strPublicationName + ''', @article = ''' + @strArticleName + ''''
PRINT @strSQL
EXEC(@strSQL)

IF EXISTS(SELECT 1 FROM #tmpArticleColumns) BEGIN
-- Now we have a list of the column names in the article 
-- along with the published flag
INSERT INTO #tmpArticlesWithTextTypes
SELECT
 @strPublisherDB AS PublicationDB
,@strPublicationName AS PublicationName
,@strArticleName AS ArticleName
,so.[name] AS TableName
,sc.[name] AS ColumnName
,st.[name] AS DataType 
FROM
sysobjects AS so
INNER JOIN syscolumns AS sc ON
(so.[id] = sc.[id])
INNER JOIN #tmpArticleColumns AS ac ON
(sc.[name] = ac.ColumnName)
INNER JOIN systypes AS st ON
(sc.xtype = st.xtype)
WHERE
so.[name] = @strSourceObject
AND ac.PublishedFlag = 1
AND st.[name] IN('text', 'ntext', 'image') -- Is this all we want to find?
END

SET @lngPKID = @lngPKID + 1
END
GO

DROP TABLE #tmpArticles
DROP TABLE #tmpArticleColumns
GO

SELECT * FROM #tmpArticlesWithTextTypes
GO

DROP TABLE #tmpArticlesWithTextTypes
GO