Find Column Name Usage

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


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • 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.

  • Thanks for the tip!


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • 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 3 (of 3 total)

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