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