I found this very useful. I am constantly looking for a particular element name.

I took some liberties and enhanced my version of the code to look across all database on the server; using a cursor to store db names.

My code enhancement is shown below.

Arnie Stewart

DECLARE @DB AS VARCHAR(25)

DECLARE @CNT AS INT

DECLARE @SQL NVARCHAR(4000)

DECLARE @vcColumnName varchar(100)

--

--

SET @vcColumnName = 'PAT_NM'

--

--

DECLARE C1_CURSOR SCROLL CURSOR FOR

SELECT NAME AS DB

FROM MASTER.dbo.sysdatabases

WHERE NAME NOT LIKE 'MASTER' AND

NAME NOT LIKE 'TEMPDB' AND

NAME NOT LIKE 'MODEL' AND

NAME NOT LIKE 'MSDB'

ORDER BY

NAME

TRUNCATE TABLE ADHOC.DBO.TBL_IT_COLUMNS_DBASE_LOOKUP

SET @CNT = 0

OPEN C1_CURSOR

FETCH NEXT

FROM C1_CURSOR

INTO @DB

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL='INSERT INTO ADHOC.DBO.TBL_IT_COLUMNS_DBASE_LOOKUP

SELECT DISTINCT ''' + @DB + '''AS DBASE, SUBSTRING(o.NAME,1,60) AS [Table Name]

FROM ' +@DB+'.dbo.sysobjects o

INNER JOIN ' +@DB+'.dbo.syscolumns c

ON o.ID = c.ID

WHERE c.name = ''' + @vcColumnName + '''

AND o.XTYPE = ''U''

ORDER BY [Table Name]'

EXEC(@SQL)

FETCH NEXT FROM C1_CURSOR INTO @DB

END

CLOSE C1_CURSOR

DEALLOCATE C1_CURSOR

SELECT * FROM ADHOC.DBO.TBL_IT_COLUMNS_DBASE_LOOKUP