Find Column Name Usage

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by kgayda » Find Column Name Usage

Find Column Name Usage

Rate Topic

Display Mode

Topic Options

Author

Message

kgayda

Posted Monday, October 01, 2007 4:32 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

Comments posted to this topic are about the item Find Column Name Usage

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #405308

Andrew_Webster

Posted Monday, October 08, 2007 9:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 05, 2012 1:00 PM
Points: 128, Visits: 32

Try this for a trick: use Excel/MS Query to open INFORMATION_SCHEMA.COLUMNS then use Autofilter to create a simple data dictionary tool that does something very similar to your script.

Post #408029

kgayda

Posted Wednesday, October 10, 2007 1:25 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 5:10 PM
Points: 275, Visits: 168

Thanks for the tip!

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

Post #409226

Arnie Stewart

Posted Monday, October 22, 2007 10:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 12:40 PM
Points: 3, Visits: 43

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

Post #413476

« Prev Topic | Next Topic »

Permissions