Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find Column Name Usage Expand / Collapse
Author
Message
Posted Monday, October 1, 2007 4:32 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Monday, October 8, 2007 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 5, 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
Posted Wednesday, October 10, 2007 1:25 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Monday, October 22, 2007 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse