Find Column Name Usage

  • kgayda

    SSCrazy

    Points: 2157

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


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • Andrew_Webster

    Mr or Mrs. 500

    Points: 570

    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.

  • kgayda

    SSCrazy

    Points: 2157

    Thanks for the tip!


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • Arnie Stewart

    Grasshopper

    Points: 15

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply