October 1, 2007 at 4:32 pm
Comments posted to this topic are about the item Find Column Name Usage
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
October 8, 2007 at 9:05 am
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.
October 10, 2007 at 1:25 pm
Thanks for the tip!
Karen Gayda
MCP, MCSD, MCDBA
gaydaware.com
October 22, 2007 at 10:01 am
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